Excel Macro: Insert Date and Time in a Cell (4 Examples)

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.

To do that,

❶ Press ALT + F11 to open the VBA Editor.

❷ Go to Insert >> Module.

Create a Module: Manually Insert Date and Time in a Cell Using Macro in Excel

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

Manually Insert Date and Time in a Cell Using Macro in Excel

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.

Macro: Manually Insert Date and Time in a Cell Using Macro in Excel

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

Create a User-Defined Function to Insert Date and Time Automatically in a Cell

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.

Output: Create a User-Defined Function to Insert Date and Time Automatically in a Cell

Read More: How to Insert Dates in Excel Automatically (3 Simple Tricks)


Similar Readings


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.

Input a Command Button: Use of VBA Now function to Insert Date and Time in a Cell

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

Use of VBA Now function to Insert Date and Time in a Cell

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.

Change Command Button Name: Use of VBA Now function to Insert Date and Time in a Cell

❻ 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:

Output: Use of VBA Now function to Insert Date and Time in a Cell

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.

Output: Update Date and Time in a Cell Whenever a Change Occurs in Your Worksheet

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.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo