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

Asked By JasonK on 02-Feb-10 11:14 AM
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 replied to JasonK on 02-Feb-10 12:10 PM
I am 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
--
Cheers,
Ryan
Gord Dibben replied to JasonK on 02-Feb-10 01:35 PM
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 replied to Gord Dibben on 03-Feb-10 11:50 PM
Gord,

Thanks for your help.  I cut and paste your suggestion into the code
page following your directions and it did not 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 cannot get it to work with A and B as
you have it written.

I do not know why.  I am only running 2003 because that has been so much
easier for me to use, but I cannot seem to get this problem solved.

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

JasonK
Gord Dibben replied to JasonK on 04-Feb-10 12:35 PM
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 replied to Gord Dibben on 04-Feb-10 01:43 PM
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 replied to JasonK on 04-Feb-10 06:46 PM
Good to hear.

Gord
JasonK replied to Gord Dibben on 07-Feb-10 06:01 PM
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 replied on 08-Feb-10 06:13 PM
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 replied to JasonK on 08-Feb-10 06:22 PM
Did not read this before I posted my first reply.

Glad to hear you are sorted.


Gord