Date functions

[jknapp1005]

I'm trying to get a date function to return. If I put in NOW or TODAY it
returns a date of a passed or failed unit, but when I close the sheet out and
reopen, it refreshes the date. I need what's returned to remain the date of
the event. Formula I'm using is:

=IF(C101="PASS",TODAY( ))

 

[Gord Dibben]

NOW and TODAY are volatile functions and will update.

Either don't use them..........manually enter the date with CTRL + ;

Or use VBA to to enter a static date when C101 has "PASSED" typed into it.

Example code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("C:C")) Is Nothing _
And Target.Value = "PASSED" Then
Target.Offset(0, 1).Value = Now()
End If
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the code into that module.

When PASSED is typed into any cell in column C, the date and time will be
entered in Column D


Gord Dibben MS Excel MVP

 

[T. Valko]

See this:

http://www.mcgimpsey.com/excel/timestamp.html