We use timestamps when we want to track activities. It is a type of data type that determines the date and time. In our article below, we illustrate the different methods you can use to insert a timestamp when a cell changes in Excel
Using a keyboard shortcut
1. Select the cell where you need to insert a timestamp in an open worksheet.
2. Afterward, use the keyboard shortcut key Ctrl +; do this by pressing and holding the control key first, then press the semicolon sign.
3. Your cell will be in edit mode. Press Ctrl + Shift +; keys. Press and hold the control and shift keys simultaneously, then press the semicolon key.
4. Press on the Enter key to complete your entry. You will notice that you have entered the current time and date as per your system’s date.
Using Excel functions
You can use functions such as IF, NOW, and AND. Follow this simple example.
Using NOW Function
1. In an open Excel workbook, right-click on the cell that you want to insert a timestamp.
2. Click on the option ‘Format Cells’ in the context menu to open a dialog box.
3. In the displayed Format Cells dialog box, click on the Number tab.
4. Under the Category section, select Custom.
5. Under the Type section, select a date format. You can select any format with hours minutes seconds and AM/PM
6. Click the OK button. Doing so will ensure that the result shows the date as well as the time.
7. Type the following function and press enter and the timestamp will show automatically
=now()
Combine if and now function to insert a timestamp
For example, you have column A as the data entry column to input data. You want to timestamp in an adjacent cell in column B.
1. In an open Excel worksheet, open Options by clicking on the File Tab then options.
2. In the displayed window, click on the Formulas option on the left side pane.
3. Navigate to Formulas>>Calculation options section, Tick Enable iterative calculation box.
4. Next, set your Maximum Iterations to one.
5. Click the OK button.
6. Afterward, enter the formula below in your destination cell (cell B2, the first cell of your timestamp entries) and enter the formula to other cells under it by dragging the handle
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
7. Drag down the formula to the desired cells
8. the Ctrl + 1 keys on your keyboard to launch Format Cells
9. Under the Category section, open Custom tab and choose your desired time format
8. Now enter the data in the first column, and the timestamp will appear automatically in the second column
4. Click on the OK button.
Download how to timestamp in Excel when a cell changes the practice sheet
Excel timestamp practice sheet
Using VBA code
VBA code allows users to customize their own functions. You can add a timestamp using Visual Basic Application. Lets get started
1. Open the VBA window using the ALT+F11
2. Click Insert>Module and add the following VBA
Function MyTimestamp(Reference As Range) If Reference.Value <> "" Then MyTimestamp = Format(Now, "dd-mm-yyyy hh:mm:ss") Else MyTimestamp = "" End If End Function
Save the workbook as .XLSM
3. Enter the following function in the workbook in cell B1
=MyTimestamp(A1)
4. Drag the formula to your desired cells
5. Now enter your data in the first column
6. The timestamp will update automatically as you type data
Use Microsoft Excel for the Web or Google Sheets
Both Microsoft 365 and Google Sheets run on the cloud. This means they update a timestamp every time a cell changes. In addition, they also, also store the data of the user that changed such as name. Lastly, you can view the version history and all the changes that have been made since you created the spreadsheet
Similar Reads
CAN YOU TIMESTAMP WHEN A CELL CHANGES FROM CURRENT DATA TO NEW DATA? THIS ONLY WORKED FOR ME WHEN THE CELL HAD NO DATA IN IT TO BEGIN WITH. IT DID NOT WORK WHEN I SUBSEQUENTLY CHANGED THAT DATA IN THE CELL.
Hi Serena, You can make use of the VBA code to timestamp when a cell changes from current data to new data.
Alternatively you can use If and Now function.
Check the practice sheet and see the the timestamp will update whenever the current data changes to new data.
Hi, the cell formula seems to work, but when I close the spreadsheet and open it there’s a malfunction. When I change the data in cell A2, it places the timestamp in cell B2 as desired, but it also updates every cell in the B column, even though these were previously working and had different timestamps with the correct formula and references programmed in all the A column cells.
This seems like an excel bug.
Yep! This used to work for me, but in Teams excel at least there isn’t an option anymore under “Calculation options” for anything to do with iterative. Additionally, the formula used to work to timestamp when a cell was updated, but now whenever the file is opened any cell that includes the “NOW()” function in any way automatically updates to the current date.
Seems like it was probably an intentional update to the way functions work! Too bad it makes the function I Was using useless.
The macro seems to be working fine, nicely done!