Time or row cell updated
 

I have an XLS which is updated throughout the day with new rows of data.
I want to capture the time each row is input. Ideally when a specific
cell is input I want to put the associated time at the end of the row
soI can calculate the time between each row. I have tried the obvious
=NOW() function but ever time the sheet recalculates the times change.


Is there a way to capture the current time but not update it when the
sheet re-calculates?




--
525047

Time or row cell updated
 

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a 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 = Format(Now, "h:mm:ss AM/PM")
End If
End If
enditall:
Application.EnableEvents = True
End S

As you enter data in column A, the date/time will be entered in column B

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

Copy/paste into that sheet module. Adjust to suit. Alt + q to return to the
Excel window.


Gord Dibben MS Excel MVP

On Sat, 22 Mar 2008 22:59:42 +0000, 525047
wrote:


I have an XLS which is updated throughout the day with new rows of data.
I want to capture the time each row is input. Ideally when a specific
cell is input I want to put the associated time at the end of the row
soI can calculate the time between each row. I have tried the obvious
=NOW() function but ever time the sheet recalculates the times change.


Is there a way to capture the current time but not update it when the
sheet re-calculates?

Time or row cell updated
 

Try this if you don't want to use a macro. Change accordingly:
=IF(B3="","",IF(C3="",NOW(),C3))
Tool/Options/Calculations/Check Aterations

"525047" wrote:


I have an XLS which is updated throughout the day with new rows of data.
I want to capture the time each row is input. Ideally when a specific
cell is input I want to put the associated time at the end of the row
soI can calculate the time between each row. I have tried the obvious
=NOW() function but ever time the sheet recalculates the times change.


Is there a way to capture the current time but not update it when the
sheet re-calculates?




--
525047