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 last time you made a change in cell C1.
In the cell where you want a "fixed" timestamp (say, A1), type the
following formula:
=IF(LEN(C1),NOW(),NOW())
Pic 2: Write this formula (in cell A1) to show you the last time cell C1 was edited
Result: The timestamp in A1 will change only if you make changes to C1. Do not "touch" any other cell and do not press the F9 key.
Whenever you change the value of C1 (even if you press the Delete key within that cell), the timestamp in A1 will update.
😊😊😊
אין תגובות:
הוסף רשומת תגובה