I am creating a spreadsheet application on Excel 2000 to record the time (24
hour clock) when checks are made each day of the week by a persons
identification. To accomplish this, a logical test is used in the column
(say Column D) for recording the time the person made the check (say Column
E). When the validity of the persons identity is verified, the time is
recorded.

=IF(AND(E14200,E14<226),NOW(),€� €œ) The problem is, when the Value_if_true
when using NOW(), records a €˜volatile time, i.e., the time is not retained
for that entry, when the test is performed on another day. All times in the
column change to the current NOW(). Once entered, it is intended the time
entered in a Row will not change when subsequent persons identity is
verified.

Can you help?

Take a look he

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


You'll have to modify the macro somewhat. Perhaps one way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim bValid As Boolean
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("E2:E100"), .Cells) Is Nothing Then
If IsNumeric(.Value) Then _
bValid = (.Value 200) And (.Value < 226)
On Error GoTo ErrorExit
Application.EnableEvents = False
With .Offset(0, -1)
If bValid Then
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
Else
.ClearContents
End If
End With
ErrorExit:
Application.EnableEvents = True
End If
End With
End Sub


In article ,
retiredguy wrote:

I am creating a spreadsheet application on Excel 2000 to record the time (24
hour clock) when checks are made each day of the week by a persons
identification. To accomplish this, a logical test is used in the column
(say Column D) for recording the time the person made the check (say Column
E). When the validity of the persons identity is verified, the time is
recorded.

=IF(AND(E14200,E14<226),NOW(),€� €œ) The problem is, when the Value_if_true
when using NOW(), records a €˜volatile time, i.e., the time is not retained
for that entry, when the test is performed on another day. All times in the
column change to the current NOW(). Once entered, it is intended the time
entered in a Row will not change when subsequent persons identity is
verified.

Can you help?

Thank you, but your recommendation still results in an error message.
Comparing your initial reply with the section 'Using a worksheet event macro'
from your Time and Date Stamps article, you apparently attempted to create a
macro to satisfy my needs. I have had very little experience with macros and
obviously can't pick up on the error myself. I also applied 'Using circular
references and worksheet functions' from the same article which produced the
desired results, but without the validity limitation of the of the values -
200 and <226.

What am I not seeing? Thanks again

"JE McGimpsey" wrote:

Take a look he

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


You'll have to modify the macro somewhat. Perhaps one way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim bValid As Boolean
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("E2:E100"), .Cells) Is Nothing Then
If IsNumeric(.Value) Then _
bValid = (.Value 200) And (.Value < 226)
On Error GoTo ErrorExit
Application.EnableEvents = False
With .Offset(0, -1)
If bValid Then
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
Else
.ClearContents
End If
End With
ErrorExit:
Application.EnableEvents = True
End If
End With
End Sub


In article ,
retiredguy wrote:

I am creating a spreadsheet application on Excel 2000 to record the time (24
hour clock) when checks are made each day of the week by a person€„¢s
identification. To accomplish this, a logical test is used in the column
(say Column D) for recording the time the person made the check (say Column
E). When the validity of the person€„¢s identity is verified, the time is
recorded.

=IF(AND(E14200,E14<226),NOW(),€� €œ) The problem is, when the Value_if_true
when using NOW(), records a €˜volatile€„¢ time, i.e., the time is not retained
for that entry, when the test is performed on another day. All times in the
column change to the current NOW(). Once entered, it is intended the time
entered in a Row will not change when subsequent person€„¢s identity is
verified.

Can you help?