Table of Contents
The process of inserting a timestamp using VBA in code involves utilizing the VBA “Now” function, which retrieves the current date and time from the system clock. This function can be incorporated into the code at the desired location to capture the exact moment the code is executed. Additionally, the format of the timestamp can be customized using the “Format” function in VBA. By properly incorporating these functions into the code, a timestamp can be easily inserted and displayed in a desired format.
Insert a Timestamp Using VBA (With Example)
You can use the following basic syntax in VBA to insert a timestamp into a particular cell in Excel:
Sub InsertTimestamp()
Range("A1").Value = Format(Now, "mm/dd/yyyy hh:mm:ss")
End Sub
This particular macro inserts the current time formatted as mm/dd/yyyy hh:mm:ss into cell A1.
Note: The Now function in VBA returns the current date and time according to your computer’s system date and time.
The following example shows how to use this syntax in practice.
Example: How to Insert Timestamp Using VBA
Suppose we create the following macro to insert the current date and time as a timestamp into cell A1 of our current sheet in Excel:
Sub InsertTimestamp()
Range("A1").Value = Format(Now, "mm/dd/yyyy hh:mm:ss")
End Sub
When we run this macro, we receive the following output:
Cell A1 now displays the current date and time when this macro was run.
For this particular example, the datetime is formatted as mm/dd/yyyy hh:mm:ss.
Note that we could also specify a different format to use.
For example, we could create the following macro to display the current time in the format dd-mm-yyyy hh:mm:ss in cell B1:
Sub InsertTimestamp()
Range("A1").Value = Format(Now, "mm/dd/yyyy hh:mm:ss")
Range("B1").Value = Format(Now, "dd-mm-yyyy hh:mm:ss")
End Sub
When we run this macro, we receive the following output:
Note: You can find the complete documentation for the Format function in VBA .
Additional Resources
The following tutorials explain how to perform other common tasks in VBA: