I want to create a spreadsheet that automatically enters the date that I started a task when I enter text into a field...that's simple enough with:

=IF(A2<>"",NOW(), "")

But that will change the date everytime I open the spreadsheet. I'm trying to set it up so the current date is placed in a cell and then stays that way.

So for example, I enter text in a cell, the date 11/10/2006 is entered in another cell and the date is fixed to 11/10/2006. I don't want it to change to 11/11/2006 when I open it tomorrow. Obviously the above formula will change the cell to whatever the current date is each time I open the spreadsheet.

Howdy. Chip Pearson has some excellent help for this kind of work:

Chip Pearson's site

How about applying a macro under worksheets:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'adjust e2 to where your formula is.
Range("E2").Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub

Hi Andre,

Or
beeyule, if you can describe more particulars of the situation, maybe a simple bit of code could achieve what you want. Such as this code (when in the code module for a worksheet) will enter the date in column A every time a change is made to a cell in a different column. Something similar can be done maybe to suit your requirements?

regards,
Fazza