Formula that will record the time and date when an entry is made on a sheet

[janderson]

Hi, is there a formula that will record the time and date that an
entry was made on a sheet. I am trying to make a telephone log that
will show me which customers called for which salesmen, so rather than
entering the time and date against each call entry I wondered if
there was a function that would record the time and date that a cell
was editted. Wondered if I could use the "=now()" function, but as
time passes the sheet simply updates! Any insights much appreciated -
John

 

[Guest]

Hi,

you can enter a static date with

Ctrl+;

or a time with Ctrl+Shift+;

or another way is with a macro. Say you are entering data in A1 to A10 this
will put the date and time in column B

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then 'change to
suit
Target.Offset(0, 1).Value = Format(Now, "dd/mm/yy hh:mm:ss")
End If
End Sub

Right click the sheet tab, vuew code and paste this in

Mike

 

[Gord Dibben]

You would need event code to enter a static date/time.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module. Adjust Me.Range("A1:A10") to suit.


Gord Dibben MS Excel MVP

 

[janderson]

Hi, is there a formula that will record thetimeanddatethat an
entry was made on a sheet. I am trying to make a telephone log that
will show me which customers called for which salesmen, so rather than
entering thetimeanddateagainst each call entry I wondered if
there was a function that would record thetimeanddatethat a cell
was editted. Wondered if I could use the "=now()" function, but astimepasses the sheet simply updates! Any insights much appreciated -
John

Many thanks! Works like a dream. Cheers - John