Entering times & dates

  • Thread starter Thread starter John Hagerman
  • Start date Start date
J

John Hagerman

I want to be able to enter a time & date stamp when a user enter a value
into another field, and never change. The problem I am having using the
NOW() function is every time they enter on another line all time/date values
are updated.Any help would be appreciated.

Thanks
John
 
John

Manually enter today's date by CRTL + ;(semi-colon) and it won't change.

Manually enter the time by CRTL + SHIFT + ;(semi-colon) and it won't change.

Using an event change code in the worksheet.
Right-click on the sheet tab and "View Code".

Copy/paste the code in there.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in any cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Format Column B to whatever you wish to see.

Gord Dibben Excel MVP
 
If you're typing =now() into that cell, try these two shortcuts instead:

ctrl-; (spacebar) ctrl-:

ctrl-semicolon (space character) ctrl-colon

You could also use an event macro that looks for changes to your worksheet. I
used column A as the range that got the value and column B (.offset(0,1)) as the
column that got the date/time stamp:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False

With Target
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Right click on the worksheet tab that needs this behavior. Select view code and
paste this in the code window.

Back to excel and try it out.

(I also emptied the time/date if you cleared the value in column A.)
 
Back
Top