When you work with Excel, sometimes you may want to enter the current date or time near the list of items, such as expenses or investments. This operation is called timestamping. There are a few methods you can use to do it.
TODAY and NOW
These functions take no arguments. The TODAY function returns the current date and the NOW function returns both the date and time.
The problem with these functions occurs when we use the F9 key. Each time you do this the value is refreshed, so this may not be the best option for our example.
Keyboard shortcut
Another, even faster method is to use keyboard shortcuts. The first one is Ctrl + ;. You can use this one to insert the current date. The second one is Ctrl + Shift + ;. It can be used to insert the current time. Both shortcuts insert data as a number, so it won’t change when you refresh the worksheet (F9).
Circular reference
Instead of using keyboard shortcuts every time, a better way is to use a circular formula.
A circular reference occurs when a formula refers to itself or another cell that it depends on.
But before you use one of the following examples, you have to enable iterative calculations. In order to do it, go to FILE >> Options >> Formulas >> Calculation Options and check to Enable iterative calculation. Change Maximum iterations to 1.
Example 1:
The first example shows a formula that refers to itself.
|
1 |
A1: =A1+1 |
Each time you refresh the worksheet (F9), the value is increased by 1.
Example 2:
Cell A1 refers to cell A2, but A2 refers to A1.
|
1 |
A1: = A2 |
|
1 |
A2: = A1+1 |
When you refresh the worksheet, cell A1 will always be lower by 1 than A2.
Circular Timestamp
This formula will be used to generate our timestamp.
|
1 |
A1: =IF(B1="","",IF(A1="",NOW(),IF(A1=0, NOW(),A1))) |
It may seem a little complicated. Let me explain.
Analysis:
- If there is no value in cell B1 then do nothing.
- If there is a value in cell B1, check if there is a value inside A1 (current cell).
- If there is no value in cell A1, then insert the current date and time (NOW).
- If there is a value, but the value is 0, then insert NOW.
- If the value is different than 0, keep the value (A1).
Enter the formula in cell A1 and press Enter. Nothing happens. Now, inside cell B1 insert some data and refresh the worksheet.
In cell A1, instead of time and date, there is a number. It happens this way because Excel treats data and time as numbers. If you want to know more about it, read the lesson about dates and time formatting and another one about the method which Excel uses to store dates and times.