10

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.

  • +1 Like the second answer better than the first. – DaveParillo Mar 28 '10 at 4:55
  • 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. – MickTaiwan Mar 29 '10 at 10:28
  • 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 Apr 24 '17 at 20:14
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.

protected by Community Mar 16 '16 at 17:16

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?