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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

janderson@sgt.co.uk - 09 Oct 2007 12:35 GMT

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

Reply to this Message

Mike H - 09 Oct 2007 13:17 GMT

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

> 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
[quoted text clipped - 4 lines]
> time passes the sheet simply updates! Any insights much appreciated -
> John

Reply to this Message

Gord Dibben - 09 Oct 2007 16:18 GMT

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

>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
[quoted text clipped - 4 lines]
>time passes the sheet simply updates! Any insights much appreciated -
>John

Reply to this Message

janderson@sgt.co.uk - 11 Oct 2007 08:28 GMT

On 9 Oct, 12:35, jander...@sgt.co.uk wrote:

> 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
[quoted text clipped - 3 lines]
> 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

Reply to this Message