formula to place real time in a cell when data entered in an adjacent cell

[JasonK]

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

 

[Ryan H]

I'm not sure which column you are concerned with so I assumed Col. A is where
users enter data and Col. B is were you want the time stamp to go in, right.
This macro should be placed in the worksheet module your users will be
entering the data. Make sure you format Col. B to the time or date format
you want to show in the cell. Then enter something anywhere in Col. A and
watch what happens.

Hope this helps! If so, let me know, click "YES" below.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range

Set MyRange = Application.Intersect(Target, Range("A:A"))

If Not MyRange Is Nothing Then
Target.Offset(, 1).Value = Now
End If

End Sub

 

[Gord Dibben]

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

 

[JasonK]

Gord,

Thanks for your help. I cut and paste your suggestion into the code
page following your directions and it didn't work for me.

I used columns A and B with the intent to edit it to the columns I
need when I got it working, but I can't get it to work with A and B as
you have it written.

I don't know why. I'm only running 2003 because that has been so much
easier for me to use, but I can't seem to get this problem solved.

Thanks for your help again, and if you can think of anything I'm doing
wrong, please let me know.

JasonK

 

[Gord Dibben]

Was tested before posting and results were as advertised.

Can't get it to work means what?

Nothing at all?

Error message?

You sure you pasted into the worksheet module, not a general module?

You sure you are entering something in any cell of Column A?

Perhaps events have been disabled by some other code that ran?

Start by re-enabling events just in case.

When in the code window hit View>Immediate Window

Paste this in then hit ENTER

Application.EnableEvents = True

Now try entering something in Column A

If no joy send the workbook to gorddibbATshawDOTca change the obvious


Gord

 

[JasonK]

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

 

[Gord Dibben]

Good to hear.

Gord

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

 

[JasonK]

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

 

[JasonK]

Sorry Gord, I got it.

It was the syntax I was using.

I used....= Format(Now + ((Cells(1,6).value) * .00001157), "mm/dd/yy
hh:mm:ss")
and it worked perfectly.

thanks for your time.

JasonK

 

[Gord Dibben]

This is the closest I can get.

F1 contains the number 10

Me.Range("A" & n).Value = Format _
(Now + (Range("F1") / 86400), "mm/dd/yy hh:mm:ss")

Adds 10 seconds to NOW() in your timestamp.


Gord

 

[Gord Dibben]

Did not read this before I posted my first reply.

Glad to hear you're sorted.


Gord