11
2
In an Excel spreadsheet, I'd like to automatically update a cell with the current date and time when another cell is changed (like an update timestamp).
There is a timestamp for each row. I'd like to update them as soon as one of the preceding cells in the row is updated.
Do you have any pointers on how to do that?
9
Create a cell with the value Now(). Format it how you want - like yyyy/mm/dd HH:mm:ss for a full timestamp in 24 hour time.
Now, as long as auto-recalculate (the default) is set, any time another cell is changed, you'll get a fresh timestamp.
Upon further reflection, if you desire a hardcoded timestamp, which is only updated by actions not including such things as open workbook, print, etc. you could create a macro attached to worksheet_change. Set the target cell as text, with specific formatting like before. Use something like: Sheet1.Cells[].Value = Text(Now(), "yyyy/mm/dd HH:mm:ss") on the Worksheet_Change event.
If you put this into a template that loads at startup, you'll never have to think about it again.
wbogacz
Posted 2010-03-27T18:35:49.613
Reputation: 302
+1 Like the second answer better than the first. – DaveParillo – 2010-03-28T04:55:50.227
I have updated the question to better reflect the need: There is a timestamp for each row. I'd like to update them as soon as one of the preceding cells in the row is updated. So I think that a macro is needed here as you mentionned. I will search that way. Thanks. – None – 2010-03-29T10:28:09.973
It should be noted that putting code into the Worksheet_Change() event will cause Excel to wipe out the undo stack every time it executes. So if you are updating values elsewhere on the sheet this solution will essentially disable undo for this worksheet as a side-effect. – Ross McConeghy – 2017-04-24T20:14:50.863
8
http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/ has details on how to implement timestamps using recursive formula. They work a treat.
The article outlines how to first turn on circular formula (off by default) and then how to create a circular formula that automatically inserts a timestamp when a specific cell has a value inserted.
If the cell to contain the timestamp is B3 and the cell to watch is C3 the formula is:
=IF(C3<>"",IF(B3="",NOW(),B3),"")
That is, when C3 is not empty, set B3 to be NOW() (if B3 was empty) otherwise the current value of B3.
This appears stable over updates and saves.
Jonathan Wright
Posted 2010-03-27T18:35:49.613
Reputation: 181
1
Possible duplicate of How to insert the =now() function in a cell automatically when I write in another and hit enter
– Raystafarian – 2015-12-10T17:12:45.463@Raystafarian: We've got circular dupes going on. This is the older question. If both get closed, we'll lock out all new answers. – fixer1234 – 2015-12-11T05:38:01.773