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

Solution 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.

Solution 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.