Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
1  
    
@Raystafarian: We've got circular dupes going on. This is the older question. If both get closed, we'll lock out all new answers. – fixer1234 Dec 11 '15 at 5:38

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

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

You must log in to answer this question.

protected by Community Mar 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?