Inserting current time

Posted by John R on February 13, 2002 4:32 AM

I am trying to put together a spreadsheet to record the time an entry is made into a neighboring cell.

Ideally as I enter the values in a1, the time of entry will appear in a2. I have tried using
=IF(A1=0,".",NOW())for a2, but when I enter the next value in b1, the time in a2 is updated to the current time, and not left as the time a1 was enetered.

Is there anyway to get excel not to update previously entered times, or is there another way round this problem?

Posted by Derek on February 13, 2002 5:28 AM

John
This worksheet macro automatically puts the time in A2 when you make an entry in A1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Target.Offset(1, 0).Value = Time
End If
End Sub

If you are making many entries in Row 1 and you want the time under each cell in Row 2, then you can use this worksheet macro.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then
Target.Offset(1, 0).Value = Time
End If
End Sub

It will display the long time format, change cell formats to the time format you desire.

To make a worksheet macro, copy the macro text from here, right mouse click your worksheet tab and select View Code. Paste your macro in the big white area. Close window to return to your worksheet

Hope this helps you
Derek

Posted by Prospero on February 13, 2002 5:42 AM

Put this in the Sheet's code module :-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then [A2].Value = Now
End Sub

Posted by John R on February 13, 2002 7:49 AM

Thanks very much for your help.

Problem Solved - works perfectly.