Recording and saving dates in Excel

[Bobito]

I use the formula =if(<cell_ref>="X";=today();"") to determine when a report
was changed. However, every time I open the file, the formula always returns
todays date (naturally !!).
How do I record and save as a fixed entry the date the <cell_ref> was changed?

 

[Bernie Deitrick]

Bobito,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. Change the A2:A1000 to the range address of the cells with the formula
described below, and anytime they first calculate to show a date and not "", they will automatically
convert to a date value.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Dim myC As Range
For Each myC In Range("A2:A1000")
If myC.HasFormula And myC.Value <> "" Then myC.Value = myC.Value
Next myC
End Sub