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?
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.
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.
12 years later... here's a summary of four current methods to timestamp in Excel!
VBA Method
This type of time automatic timestamping is only truely possible with writing some very basic VBA in the background of Excel. Look for instance at this link to get you started:
https://docs.microsoft.com/en-us/office/troubleshoot/excel/run-macro-cells-change
Unfortunately VBA macros aren't always an option especially if the file will be shared. Some enterprise security settings block macros completely, not to mention the challenge to code VBA if you are not familiar with basic programming.
Data Validation Timestamping method
Luckily there are a few workarounds for timestamping in Excel without VBA. The one is to use Data Validation of the list type to "sample" the current time. Set one reference cell or named range equal to =TODAY() or =NOW() which will auto update every time you edit any cell (i.e. recalc) as NOW/TODAY is a volatile function. Set data validation (on the data ribbon) for the date entry cell, choose list and refer to the TODAY/NOW cell. This gives a dropdown list with the time reflecting the most recent recalc. Once selected from the dropdown, it copies the time value and will "lock" it in - BUT you still need to select it yourself.
Circular formula method
The other method requires changing Excel settings to allow circular formulas/iterative calculations, which isn't ideal because it is a global setting. But it works really well and exactly like you'd expect in your question. See here:
https://howtoexcelatexcel.com/blog/create-a-timestamp-in-excel-with-formulas/
Pro's and con's
The VBA method works very reliably and most suitable for a single user or in cases where users are familiar with allowing macros based on security settings. Some basic coding skills required. Also once a macro executes, your undo history is usually lost.
The data validation method is very simple to implement and works out of the box w/o special settings or security allowances. It is however required that the user be consistent in using the dropdown cell to timestamp (after making other changes on the row).
The circular formula method reflects the timestamp automatically as you enter data in other cells but you must enable a global Excel setting for iteration with some unintuitive behaviour. While the setting is saved with the workbook, if you open any other workbook before this one the setting resets to no iteration. Opening this workbook doesn't change it back. You must open this workbook first. Also opening & saving other workbooks then saves the global setting with those files too which can become a (minor) issue. Mostly it disables error reporting upon entering circular formula in normal workbooks.
Ps. Shortcut key method
Sometimes the simplest method isn't the most ideal from the IT guy's point of view, but it works for the end user. You can press one of these shortcuts to just insert the current time or date in the current cell as a value:
CTRL and ; (to insert the current date)
CTRL SHIFT and : (to insert the current time)
