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
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
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
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
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 365 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