Gord Dibben,

You helped me with some code last week and I'm grateful. I need to
add some code to make it work a little better, and all I'm able to do
is make it gag.

Here is the code you gave me that works perfectly:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
If Me.Range("B" & n).Value < "" Then
Me.Range("A" & n).Value = Format(Now, "mm/dd/yy hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub


This code will allow me to place data in a cell, and have the
date/time stamp automatically appear in an adjacent cell.

The purpose of the code is a spreadsheet that tracks events watched
live in a casino surveillance monitoring room, placing the date time
in the proper cell so that the events can be reviewed later from DVR
recordings. The problem I have now is that the time stamp on the
computer running Excel and the time stamp running the DVRs is a little
off. I need to add a cell with an input for "seconds the recordings
are out of sync" and have it add that many seconds to the date / time
stamp so that it matches up better. A surveillance operator may watch
15 rolls of the dice and take a phone call, or log an event, and miss
several rolls. Being able to go back to the exact time of the
recording to log the missed rolls is valuable. A statistical analysis
of dice rolls is important to know if the dice are rolling true, or if
a cheating customer has used their own dice or is cheating in some
way.

I want to place the number of seconds to add or subtract to NOW (a
negative number) in cell F1 on the same page.


I tried .... format(now + (cells(f1) * .00001157), "mm/dd/yy
hh:mm:ss")
and
......=format(now+(range.cell(f,1)*.00001157), "mm/dd/yy hh:mm:ss:)
and a few other things, but nothing made it work properly.

thanks again for your help. I hope its a simple fix and that I don't
take up a lot of your time.

JasonK


On Thu, 04 Feb 2010 15:46:08 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

Good to hear.

Gord

On Thu, 04 Feb 2010 10:43:15 -0800, JasonK wrote:


Gord,

I have no idea what I was doing wrong, but the code you wrote worked
for me. I recopied it and it worked.

Thanks for your help.
JasonK



On Tue, 02 Feb 2010 10:35:07 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

I would use a sheet change event.

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 Me.Range("A" & n).Value < "" Then
Me.Range("B" & n).Value = Format(Now, "mm/dd/yy hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.

Edit columns to suit.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP

On Tue, 02 Feb 2010 08:14:12 -0800, JasonK wrote:


Still using 2003.

I have a spread sheet that records data in a column that is entered by
the user. I need a cell next to the entered data cell to reflect the
accurate date and time the data was entered.

Is there a formula I can enter into the adjacent cell that will
automatically place the instant date/time that will not update with
the next data entry?

Thanks in advance,
JasonK