Software
by pmac13 Accepted solution
by tobif Accepted solution
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.
by pmac13 Accepted solution
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.
by tobif Accepted solution
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 could format the time and enter it as text, but that would deprive you of convenient processing of the time stamps afterwards. Format(Now(), "hh.mm AMPM")
by pmac13 Accepted solution
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
by tobif Accepted solution
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...
by pmac13 Accepted solution
TobiF,
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)
by tobif Accepted solution
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
by dogknees Accepted solution
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
Regards
by tobif Accepted solution
I did, and it works fine.
If I were to create an ever updating formula, then I'd have to do:
SomeKindOfCell.formula = "=now()"
by dogknees Accepted solution
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.
Regards
by tobif Accepted solution
Whereas .value I treat rather as the current value of whatever formula the cell contains.
(So I regard .value as readonly, even though Excel has it as read/write)
This discussion is closed to new replies.
Sort by Trending