Recording time in Excel

By Pmac13 ·
I'm trying to automate my production time sheet. I would like to populate a cell with the hour & minute when information is input into an adjacent cell. Example: information is added to cell F5, the time (hour&minute) is recorded automatically in cell F4.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Access or VBA

by TobiF In reply to Recording time in Excel

In order to do this the way you describe, you'd need to write a macro that would execute every single time updates are made to a sheet.

This macro would need to somehow analyze, whether a new entry has been made, and in these cases enter a corresponding value in another cell.

I can see two nicer ways forward:
1. Use MS Access instead.
This would give you a more structured solution. In MS Access, if you define the default value of a field as =NOW(), then you'll get this function.

2. Another option would be to use a VBA macro to enter details, and have this macro stamp the current time, as well. The macro could be invoked automatically and perpetually, or via a toolbar button or keyboard shortcut. Another option could be to invoke the macro from a form.

If you want more detailed help, then you need to tell us what way you want to go and what version of office you're using.

Collapse -


by Pmac13 In reply to Access or VBA

Thanks TobiF,

VBA is my only option and I'm running the 2000 version of excel.

A macro sounds right. If possible I would like it executed automatically when numbers are entered into a cell. Entering numbers (765437) in cell B3 starts macro which populates time (10:25am) in cell B2.

Collapse -

Here we go

by TobiF In reply to VBA

Right click the the worksheet name tab and select "view code"

In the sheet code window, paste the following code:
Private Sub Worksheet_Change(ByVal source As Range)
' runs when the sheet is changed
' source points to changed range
If source.Column = 3 Then
source.Offset(0, 1).Formula = Now()
End If
End Sub

From now on, if anything is entered into a cell in column C, then the current time will be entered in the cell next to the right (in column D)

If you want to enter the time in the cell above the changed cell, then the offset (-1,0) shall be used instead.

Note that the current time is entered in Excels native format (i.e. serial number for the day and decimal part for the time. Use cell format to select how the date and/or time is shown.)
Yes, you can do the reformatting in the macro, as well. But if the time entries are always in the same column, then I see no reason to include that in the macro.

(and, again, we <i>could</i> format the time and enter it as text, but that would deprive you of convenient processing of the time stamps afterwards. <i>Format(Now(), " AMPM")</i>

Collapse -


by Pmac13 In reply to Here we go

TobiF, This works but the format won't hold. I hi-light the column and change the time format to what I want but the next entree inputs in the original format.

I'm doing this to capture production off printing presses. So there is "start run" - "end-run" counts. When the operator puts in his numbers for start-run it populates a cell with a start-time. When he inputs end-run numbers I need it to populate another cell with end-time.

In my ignorance I thought I could just paste your formula in a second time changing the end column number in the formula but that did not work.

Also can we specify cells instead of doing column C? example C5 thru C20

Collapse -

Like this?

by TobiF In reply to Tweaks

Private Sub Worksheet_Change(ByVal source As Range)
' runs when the sheet is changed
' source points to changed range
If source.Row = 5 Then
With source.Offset(-1, 0)
.Formula = Now()
.NumberFormat = "h:mm:ss AM/PM;@"
End With
End If
End Sub

The code above accomodates your initial request in the following way: If anything is entered anywhere in row 5, then the current time (and date, actually) is entered in the cell immediately above. That same cell is also formatted to show the time in Amwerican format down to the second.

I didn't really understand what you mean with cells C5 thru C20. Can you be more specific?
We can check if an entry was made within this range, but where do we den put the time?
We can also enter the current time in the first empty cell in some specific range, anytime anything is entered anywhere on the sheet (perhaps not very useful, but fun...)

If you have start and stop times, then maybe it would be good to have start and stop recorded in separate rows or columns?
We can either disregard the "source" checking altogether (and always enter the time immediately to the _____ /your choice of up,down,right,left... or even on a different sheet!)

Yeah, you could build a log on a separate sheet: At this time, that value was entered into which cell...

Collapse -

More Info

by Pmac13 In reply to Like this?


Column D records start time and Column E records end time. Column F is where I input start count and column G is where I input end count.

I have it set up as follows. When I input my start count in (F5) I need (D5)to show the (start)time. When I input my end count in (G5) I need (E5) to show the (end)time. I have the sheet set up to input in each row from 5 through 20.

Above 5 the cells are used for column labels and to record names, shifts, dates etc.(no time calculation needed)

Collapse -


by TobiF In reply to More Info

Somewhere here, I really should say that it's time for yourself to give it a shot.
But, if you promise to "give me a thumb" by marking at least one of my answers as helpful, and in general behave nicely, then you're allowed to try the code below. :)

(For clarity, I included all limitations you gave in your text, even though, in real life I probably would suggest you don't limit the sheet downwards, i.e. I'd leave it active for all row numbers> 4)

Private Sub Worksheet_Change(ByVal source As Range)
' runs when the sheet is changed
' "source" points to changed range
If (source.Column = 6 or source.Column = 7) and (source.Row > 4) and (source.Row < 21) Then
With source.Offset(0, -2)
.Formula = Now()
.NumberFormat = "h:mm:ss AM/PM;@"
End With
End If
End Sub

Collapse -

That's Not Going to Work

by dogknees In reply to Here we go

Since you're adding a formula to the cell, every time the workook calculates, it will update that cell to the current date/time.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Target.Offset(0, 1) = Now
End If
End Sub


Collapse -

Did you try?

by TobiF In reply to That's Not Going to Work

I did, and it works fine.

If I were to create an ever updating formula, then I'd have to do:
SomeKindOfCell.formula = "=now()"

Collapse -

You Are Correct

by dogknees In reply to Did you try?

My mistake. I missed the quotes. What I was seeing was .Formula and although I know people do this, I've always used .Value or let it default as per my code.

I guess to me, .Formula indicates I'm putting a formula in the cell, although I realise this isn't the formal definition.


Related Discussions

Related Forums