Automatically update cell in Excel with current timestamp when another cell is changed

Question

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?

Answer 1

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.

Answer 2

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.