Quickly and automatically insert date and timestamp in Excel
In Excel, inserting date and timestamp is a normal operation. Here in this tutorial, I will introduce multiple methods on manually or automatically inserting date and timestamp in Excel cells by providing different cases.
If you just need to insert date and timestamp in a few cells, you can manually insert them by pressing shortcuts.
Insert current date: Control + :
Insert current time: Shift + Control + :
1. You can format the outputs as the date formats or time formats as you need in Format Cells dialog.
If you want to insert a date or timestamp which can update automatically, you can use below formulas.
Insert current date
Press Enter key, and current date is inserted in the cell.
Insert current time：
Press Enter key, and current date and time is inserted in the cell.
1. You can format the outputs as the date formats or time formats as you need in Format Cells dialog. For instance, you only want to display the current time, just format the cell as Time after using the =NOW() formula
2. If you want to insert current date and time into worksheet header or footer, you can use the Insert Workbook Information function of Kutools for Excel to quickly handle this job. Click to free download
Supposing, there are two columns, Column A and Column B, now you want to insert the current timestamp in the Column B while entering data in Column A, how can you do it?
Auto insert timestamp while entering data
1. Firstly, click File > Options to open the Excel Options dialog, choose Formulas in the left pane, then check Enable iteractive calculation in Calculation options group. And click OK.
2. In Column B, for instance, Cell B1, type this formula
then drag auto fill handle down to the cells.
3. Then format the formula cells as the datetime format as you need in the Format Cells dialog: keep the formula cells selected, right click to display the context menu, choose Format Cells, then the Format Cells dialog pops out, in the Custom section which under the Number tab, type the format you need in to the Type textbox, and click OK.
Now when you enter data into Column A, the current datetime will be inserted in Column B.
Auto insert and update timestamp while cell changes in another column
If you want to automatically insert timestamp while cell entry, and at the same time, if the entry change, the inserted timestamp will be updated, you can use below formula:
A1 is the cell that you will entry data, B1 is the cell of formula that you want to insert timestamp.
Drag auto fill handle down the cells that you use.
Then format the formula cells as the datetime format as you need in the Format Cells dialog: keep the formula cells selected, right click to display the context menu, choose Format Cells, then the Format Cells dialog pops out, in the Custom section which under the Number tab, type the format you need in to the Type textbox, click OK.
If you are familiar with VBA code, you can do as below:
1. Right click at the worksheet tab you use, then choose View Code from the context menu.
2. Then in the Microsoft Visual Basic for Applications window, paste below code.
VBA: Auto insert timestamp
Private Sub Worksheet_Change(ByVal Target As Range) 'UpdatebyKutools20190919 Dim xRInt As Integer Dim xDStr As String Dim xFStr As String On Error Resume Next xDStr = "A" 'Data Column xFStr = "B" 'Timstamp Column If (Not Application.Intersect(Me.Range(xDStr & ":" & xDStr), Target) Is Nothing) Then xRInt = Target.Row Me.Range(xFStr & xRInt) = Format(Now(), "mm/dd/yyyy hh:mm:ss") End If End Sub
3. Then save this code. From now on, as long as you enter data or change data in Column A, the new timestamp will be inserted into Column B.
Note: you can change A and B column and mm/dd/yyyy hh:mm:ss time format in the VBA code to match your real need.
If you want to use the defined function, you can do as below:
1. Hold Alt + F11 keys to enable the Microsoft Visual Basic for Applications window. And click Insert > Module to insert a blank module.
2. Paste below code to the new module. Then save the code and go back to worksheet.
Function FormatDate(xRg As Range) 'UpdatebyKutools20190919 On Error GoTo Err_01 If xRg.Value <> "" Then FormatDate = Format(Now, "mm/dd/yyyy hh:mm:ss") Else FormatDate = "" End If Exit Function Err_01: FormatDate = "Error" End Function
3. In the cell that will be inserted timestamp, type this formula
F1 is the cell that you will entry data or change data. Then drag auto fill handle down to the cells.
Now the current datetime will be inserted if the cell F1 entered data or updated.