Saving the correct date, using TODAY()

[SevenD]

I’m trying to use a formula to check the cell to the right, and if there’s any data in there, it fills the cell containing the formula with today’s date. Well I’d like it to fill it in with the date that the data was entered into the neighboring cell and then remain that date forever, but instead, it’s resetting the dates on those cells every time I open the sheet. <o> </o>
For example, I entered some data yesterday, and it worked swimmingly, entering the correct date of 12/15/2009, but when I opened up that sheet today, all of those dates had been changed to 12/16/2009. I’m not sure how to fix this. Here’s the formula in question:
<o> </o>
=IF(RC[1]="","",TODAY())
<o> </o>
It feels like there should be a way to convert “TODAY()” into a string before placing that date into the cell but I can’t figure out how to do that.


I also tried =IF(RC[1]="","",TEXT(TODAY(), "mm/dd/yyyy")) but that didn't work either. any ideas?

Thanks!

 

[jproffer]

This does what you want to do. Change the column number to whatever you wish.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 1 Then
      ActiveCell.Offset(-1, 1).Value = Date
   End If
End Sub

Insert this code into the worksheet module.