Another Excel Question (1 Viewer)

Is there a formula in excel that I can use to automatically add the current date, but once it is there, it will not change each day? Example: If I enter data in A1, then B1 will have today’s date entered automatically. Then tomorrow I enter info in A2 and B2 has tomorrows date entered automatically. The date in B1 did not change from today’s date.
Thanks,

Sure, you'll need to use some visual basic though. This page will help with events and it covers using worksheet|change which is what you'll need to check for A1 changing, then you'll just have B1 change it's contents to today(). Make sure to lock down B1 so it can't be manually edited by mistake later!

http://www.cpearson.com/excel/Events.aspx

Hope that helps

Thanks for the reply. It looks a little more complicated than I expected, but I will give it a try.

I would take a slightly different approach than the one Robert suggests.

It sounds like you will be updating this daily. For instance, today you will put data in A1, tomorrow in A2, etc. That would require some excessively complicated VBA, IMO (unless I'm overlooking something).

I would instead create a button and call it "Date Stamp" or something like that and require the user to click this button after entering that day's data in column A. Put something like this in the button_click event:

Sub Button_Click()
Dim rngDateCell as Range

Set rngDateCell = Range("B65536").End(xlup).offset(1,0)
rngDateCell = format(now(),"Long Date")

End Sub

You should also have some error handling and such in there, but I think that will do what you want assuming you will always use consecutive rows to input the data.

Seth,
Yes, Data is going to be entered daily on this sheet. This Button, is it located on the tool bar or in the spreadsheet? This spreadsheet is located on a company shared drive and is accessed by multiple users.

Nah, the the event I suggested can be applied to a range of cells. In this case, your range would just be the singular cell. And it'll update the date cell whenever it's altered, so there's no chance for a mistake in forgetting to press a button.

Although, just like the button you'll need to work up a copy of the code for each cell.