Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
+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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.