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.
Insert date and timestamp with shortcuts
Insert date and timestamp with formula
Formula to auto insert timestamp while entering data in another column
VBA to auto insert timestamp while entering data in another column
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 + :
See screenshot:
Tips:
1. You can format the outputs as the date formats or time formats as you need in Format Cells dialog.
2. If you have Kutools for Excel, you can insert the current date or other dates in a specified date format as you need with its Insert Date function. Click to free download
If you want to insert a date or timestamp which can update automatically, you can use below formulas.
Insert current date
=TODAY()
Press Enter key, and current date is inserted in the cell.
Insert current time：
=NOW()
Press Enter key, and current date and time is inserted in the cell.
Tips:
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
=IF(A1<>"",IF(B1<>"",B1,NOW()),"")
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:
=IF(A1<>"",IF(AND(B1<>"",CELL("address")=ADDRESS(ROW(A1),COLUMN(A1))),NOW(),IF(CELL("address")<>ADDRESS(ROW(A1),COLUMN(A1)),B1,NOW())),"")
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
=FormatDate(F1)
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.
Convert date stored as text to date in Excel
Occasionally, when you copy or import dates from other data sources to Excel cell, the date might become formatted and stored as texts. And here I introduce the tricks to convert such these dates stored as texts to standard dates in Excel.
Add/subtract half year/month/hour to date or time in Excel
To add year, month or hour to date or time is usual in our Excel daily work. Have you ever tried to add half a year, month, or hour to date or time? Here I introduce the tricks to handle with this job.
Average timestamps of day in Excel
For example, you have recorded the login timestamps of every time a specific user accessed a website in Excel, and now you want to average these timestamps for predicting the most possible time this users will access the website in future, how could you get it done?
Calculate hours between times after midnight in Excel
Supposing you have a time table to record your work time, the time in Column A is the start time of today and time in Column B is the end time of the following day. Normally, if you calculate the time difference between the two times by directly minus "=B2-A2", it will not display the correct result
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
