Excel - Fixing the Timestamp

 

Excel - Fixing the Timestamp

 

The Timestamp problem is one of the most annoying problems in Excel.

The =NOW() function returns the current timestamp (date & time).
However, since this function is a volatile function  (recalculated whenever any change is applied to the worksheet) its value will not persist.

But this can be solved if you follow these two simple steps:

Step 1:

Stop Excel from re-calculating whenever a change is made in the worksheet.

This can easily be done if you "tick" the  Enable iterative calculation, as seen in the picture:

 

Excel Options -> Formulas -> Calculation Options – Enable iterative calculation

 

Explanation: by default, Excel does not allow "self-reference" calculations (i.e. when the formula within a cell refers to the cell itself). But this "self-reference" is the crux of the solution, as can be seen in Step 2.

            Pic 1: change the Calculations options for this workbook

 

Step 2

Suppose you want to track the first time you entered data in cell A1.
In the cell where you want a "fixed" timestamp (say, C1), type the following formula:

=IF(LEN(A1)=0,"",IF(C1="",NOW(),C1))

Pic 2: The formula (in C1) will show you the first time you entered data in cell A1  

Result: The timestamp in C1 will not change even if you make changes  to A1.
It will disappear only if you erase the contents of cell A1.
After entering a new value in A1, the timestamp (in C1) will automatically re-appear in C1 and will stay there forever, whether you make changes to A1 or leave it intact.
You can add new data to the sheet, or press F9 - but the timestamp in C1 will not change nor will it disappear - as long as there's some value in cell A1.

😊😊😊