Auto insert non updating static date in excel

A circular formula refers to itself (or to another cell which in turn refers to this cell). So, each time you run the formula (by pressing F9) the value in A2 will go up by 1, just like an incrementing counter.

But here is a thing you should be aware of: There is a setting called “iterations”, it tells excel how many times it should run a formula before finalizing the result.

When you press a key combination such as Ctrl ; to insert the current date in a cell, Excel “takes a snapshot” of the current date and then inserts the date in the cell.

This will be used on every row in the table, so as data is entered into the table down the rows, each date will be captured. The TODAY function is volatile and recalculates on every calculation cycle in the workbook. Post back if you run into trouble (include your own effort).

If you want a timestamp look towards a VBA Worksheet_Change event macro that automatically puts a static Date or Now into a column when data in that row has been appended or edited.

A timestamp is something you use when you want to track activities.

For example, you may want to track activities such as when was a particular expense incurred, what time did the sale invoice was created, when was the data entry done in a cell, when was the report last updated, etc.

Or, perhaps you want to insert a date in Excel that will update automatically and always display the current date and time?

Or, maybe you want to auto fill weekdays or input random dates in your worksheet?

The only problem with it is that, when you press F9 or recalculate formulas, the now() will be changed to reflect latest date and time. A good alternative (although manual) is to use keyboard shortcuts CTRL ; or CTRL : to insert current date and time in the active cell.

Since this places the value, we don’t need to worry even if the date or time changes.

This setting wouldn’t make any sense in case of regular formulas. If not for this, excel would go in to an infinite loop and stall.

Tags: , ,