Auto insert non updating static date in excel
Now let’s see how we can use this to automatically get a date and timestamp in Excel (as shown below).
Here is the keyboard shortcut to quickly enter current Date in Excel: Control : (hold the control key and press the colon key).To automatically insert the timestamp, there is a smart technique using circular references (thanks to Chandoo for this wonderful technique).Let’s first understand what a circular reference means in Excel.If you want the timestamp to update every time the adjacent cell in Column A is updated, use the below formula (use Control Shift Enter instead of Enter): This formula uses the CELL function to get the reference of the last edited cell, and if it’s the same as the one to the left of it, it updates the timestamp.Note: When you enable iterative calculations in workbook once, it will be active until you turn it off.If you want the date and timestamp together in a cell, you can use the NOW function.
Again, since all the dates and time are stored as numbers in Excel, it is important to make sure that the cell is formatted to have the result of the NOW function displayed in the format that shows date as well as time.
This could be the case when you have a report and you want the printed copy to reflect the last update time.
To insert the current date, simply enter in the cell where you want it.
One of my readers Jim Meyer reached out to me with the below query.
This can be done using the keyboard shortcuts (as shown above in the tutorial). With shortcuts, you’ll have to manually insert the date and timestamp in Excel.
To turn it off, you need to go to Excel Options and uncheck the ‘Enable iterative calculation’ option.