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


On Tue, 10 Mar 2009 12:42:02 -0700, jknapp1005
wrote:

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( ))