The update is protected by the IF test:

If .Value < dOldP5 Then


there would be no time update unless it passes that test. To fail the test
at least either .Value has to change or dOldP5 has to change. My suggestion
was to put dOldP5 in a defined name making it even more "armor plated". You
will have to put in some msgboxes or something to tell you what is letting
the date/time get changed.

--
Regards,
Tom Ogilvy

"Mike K" wrote in message
...
Tom,
P5 has not changed since 11:45 but it updates the
time everytime the workbook is closed and reopened. I
defined the name as directed but it still updates

Mike

-----Original Message-----
I would suspect the dOldP5 gets cleared before it runs or
the value in P5
changes.

If dOldP5 is getting cleared, then you might want to
store its value in a
defined name.

go to Insert=Name=Define
in Refersto put in =19 (use the current value of P5)

in the name box put in dOldP5 and click add

Save the file.

Put this in the worksheet code module

'Public dOldP5 As Double

Private Sub Worksheet_Calculate()
dim lVal as Double
lVal =
Application.Evaluate(thisworkbook.Names
("dOldP5").RefersTo)
With Range("P5")
If .Value < lVal Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy
hh:mm:ss"
End With
Thisworkbook.Names("dOldP5").Refersto
= "=" & .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
Thisworkbook.Names("dOldP5").Refersto = _
"=" & Sheets(1).Range("P5").Value
End Sub

--
Regards,
Tom Ogilvy


"Mike K" wrote in
message
...
I just noticed a small problem. When I close the sheet
and
reopen it. The time updates

Mike

-----Original Message-----
Since P5 is a calculated cell, I'd use the Calculate
event:

Put this in the worksheet code module

Public dOldP5 As Double

Private Sub Worksheet_Calculate()
With Range("P5")
If .Value < dOldP5 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "dd mmm yyyy
hh:mm:ss"
End With
dOldP5 = .Value
End If
End With
End Sub

Put this in the ThisWorkbook code module:

Private Sub Workbook_Open()
dOldP5 = Sheets(1).Range("P5").Value
End Sub

In article ,
"Mike K" wrote:

Basically if cell P5 (my lookup table is converting
it
from a string to a number from 1-19) changes from
whatever
it was to ANYTHING else I need to record the time and
date
in another cell(say Q5). Then when it changes again
record
the new time and date in Q5

.



.