Mrinmoy Roy
There are several ways to insert a date and time in a cell in Excel. In this article, you will read 4 examples to insert date and time in a cell in Excel using Macro.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
4 Examples to Insert Date and Time in a Cell Using Macro in Excel
1. Manually Insert Date and Time in a Cell Using Macro in Excel
Here, you will use a VBA code to insert the date and time in cell C12.
❶ Press ALT + F11 to open the VBA Editor.
❷ Go to Insert >> Module.
❸ Insert the following code in the VBA Editor.
Public Sub Insert_Time_Date()
With Range("C12")
.Value = Now()
.NumberFormat = "h:mm:ss AM/PM mm/dd/yyyy"
End With
End Sub
Breakdown of the Code
- Here, I used the Range property to reference a cell. You can change it as per your need.
- Then I used the VBA Now function to return the current time and date.
- Finally, I used the NumberFormat property to format the timestamp.
❹ Come back to your worksheet and press ALT + F8 to open the Macro dialog box.
❺ Select the Macros then hit the Run button.
Now the code will insert a timestamp in cell C12 as in the picture below:
Read More: How to Insert Day and Date in Excel (3 Ways)
2. Create a User-Defined Function to Insert Date and Time Automatically in a Cell
Here, I will use VBA codes to create a user-defined function to insert date and time automatically in a cell while inserting data in another cell.
Click_here to get the VBA usage guide.
❶ Now insert the following code in the VBA Editor and Save it.
Function AutoTimestamp(xRef As Range)
If xRef.Value <> "" Then
AutoTimestamp = Format(Now, "hh:mm:ss AM/PM mm-dd-yyyy")
Else
AutoTimestamp = ""
End If
End Function
Breakdown of the Code
- Here, I used the VBA Now function to insert the current date and time.
- I used the Format function to specify the time format.
- Finally, I used the IF statement to insert a timestamp only when a referred cell address is not blank.
❷ Go back to your worksheet and insert the following formula in cell C5.
=AutoTimestamp(B5)
❸ Now press ENTER and it will return a null value.
❹ Insert data in cell B5.
Now you will see a timestamp has been inserted automatically in cell C5.
Read More: How to Insert Dates in Excel Automatically (3 Simple Tricks)
Similar Readings
- How to Insert a Date Picker in Excel (With Step-by-Step Procedure)
- Change Dates Automatically Using Formula in Excel
- How to auto populate date in Excel when cell is updated
3. Use of VBA Now function to Insert Date and Time in a Cell
In this example, I will show you to create a command button using VBA code to insert the date and time in a cell.
To do that,
❶ Go to Developer >> Insert >> Command Button.
❷ Left-click and hold your mouse button. Then drag the mouse to draw a custom button.
❸ After that right-click on the button and click on View Code.
VBA Editor will open.
❹ Now insert the following code in the VBA Editor and Save it.
Private Sub CommandButton1_Click()
Range("C12").Value = Now()
End Sub
Breakdown of the Code
- Here, I used Range property to insert a cell address.
- Then I used the VBA Now function to input the current timestamp.
❺ You can change the button caption from the Properties tab for CommandButton1.
❻ Go back to your worksheet and go to Developer >> Design Mode to shut off the Design Mode.
❼ Select a cell and click on the command button to insert a date and time in that cell.
Finally, you will see a timestamp in your selected cell like this:
Read More: Excel Automatically Enter Date When Data Entered (7 Easy Methods)
4. Update Date and Time in a Cell Whenever a Change Occurs in Your Worksheet
In this example, I will show you to use a VBA code to update the date and time in a cell. The update occurs only when a change is made in your worksheet.
To do that,
❶ Press ALT + F11 to open the VBA Editor.
❷ Double-click on your worksheet name to open a Sheet Editor.
❸ Select Worksheet instead of General.
❹ Then choose Change.
These actions will generate the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
The reason to use these settings is to make the worksheet responsive to any kind of changes.
❺ Insert the following code in the VBA Editor and Save it.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Handler
If Target.Column = 3 And Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(4, 2) = Format(Now(), "hh:mm:ss AM/PM mm-dd-yyyy")
Application.EnableEvents = True
End If
Handler:
End Sub
Breakdown of the Code
- Here, I used the VBA Now function to insert the current date and time.
- I used the Format function to specify the time format.
- Then I used the EnableEvents property to detect whether any change occurred on the worksheet or not.
- Also used Error Handler to avoid errors for Blank
- If changes occur then the timestamps are updated. Otherwise, the timestamps remain as they were before.
Now come back to the worksheet.
❻ This time if you change anything in your worksheet, the timestamps in the Timestamp column will get updated automatically.
Here, I Inserted some values in the Input column and it changes the timestamps in the Timestamp column automatically.
Again, I Inserted some values in the Input column after a while and it changes the timestamps in the Timestamp column automatically based on the current time.
Read More: How to Combine Date and Time in One Cell in Excel (4 Methods)
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.
Conclusion
To sum up, we have discussed 4 examples to insert date and time in a cell using Macro in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
