Time Stamp

[John Budraitis]

When a value is entered into a cell, I need to post a time
stamp to an adjacent cell to identify the time of entry.
ie: When a value is entered into A1, B1 would display the
entry time in HH:MM:SS.

Can anyone help?

Thanks in advance for your assistance.

 

[Gord Dibben]

John

Manually you could hit CRTL + SHIFT + ;(semi-colon) to enter the time.

Otherwise an Event Macro would be required. Below are two versions.

One will update the time in B if A is edited.
Other will not update B if A is edited.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'B will update if A is edited
On Error GoTo enditall
If Target.Cells.Column = 1 Then
N = Target.Row
If Excel.Range("A" & N).Value <> "" Then
Excel.Range("B" & N).Value = Format(Now(), "hh:mm:ss")
End If
End If
enditall:
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' B won't update if A is edited
On Error GoTo enditall
If Target.Cells.Column = 1 Then
N = Target.Row
If Excel.Range("A" & N).Value <> "" And Excel.Range("B" & N) _
.Value = "" Then
Excel.Range("B" & N).Value = Format(Now(), "hh:mm:ss")
End If
End If
enditall:
End Sub

Either of the two codes will be placed in a worksheet module. Right-click on
the sheet tab and "View Code". Paste the code in here.

Gord Dibben Excel MVP - XL97 SR2 & XL2002