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?