(This answer is based on the article
How to Quickly Insert Date and Timestamp in Excel,
where you will find more information with screenshots.)
With Excel formulas, it's next to impossible to automatically insert
a timestamp that wouldn't change when the worksheet is recalculated.
However, there exists a workaround that uses formulas while misusing
one formula-calculation feature of Excel.
This feature to be misused is
which is when a series of calculations is written in a never-ending way.
For example, setting cell
A2=A1+A2 will lead to an ever-increasing
value for cell A2, as long as the value continues to be recalculated.
Step 1 : Enable limited iterative calculation
- Open Excel File > Options > Formulas
- Under "Calculated options", enable "Enable iterative calculation"
and set the maximum to a low value (
1 is fine)
Now Excel will recalculate circular references, but will stop after
a certain number of calculations.
Step 2 : Enter the cyclic formula
Suppose that the value is to be entered in the A column,
starting from A2 and the Time column is in column B.
The formula will be :
When entering a value in column A, the timestamp would automatically
appear in the adjacent column B cell, and will not update when later
modifying the A2 cell.
Alternative for modification date
The above method works for the setting column B2 to the time that the
first value was entered in A2.
A variant formula is for the case when the time is to be updated
every time that column A2 is modified.
Use this formula: