In this tutorial, we will learn different ways to get today’s date and current time using a VBA code.
Today’s Date in VBA
In VBA, there’s a function called “DATE” that you can use to get the current date. When you use this function, just like the following example, it returns the current date according to the date setting of the system.
Sub myMacro() Range("A1") = Date End Sub
When you run this code, it enters the current date into cell A1 and uses the current format of the date that you have in Excel.
You can also use this function to get today’s date into a message box, just like the following code.
Sub myMacro() MsgBox Date End Sub
Today’s Date and Current Time
In the same way, there’s a function called “NOW” that you can use to get today’s date and current time. As you can see, we have used it in the following code to get the current date and time into cell A1.
Sub myMacro() Range("A1") = Now End Sub
Now if you want to get the current time only instead of date and time you need to use another function “Format” (you can learn more about this function from here) along with the now function.
In the above code, we have used the now function to provide the value for the format function and then used a format for the time only.
Sub myMacro() Range("A1") = Format(Now, "HH:MM Am/Pm") End Sub
Changing Today’s Date Format (Example: YYYYMMDD)
You can also use the format function to change the format of the current date. In the following code, we have used the format function and specify the format as “YYYYMMDD”.
Sub myMacro() Range("A1").Value = Format(Date, "YYYY/MM/DD") End Sub
Note: In the above code, we have used value property to enter the date into cell A1.
⇠ Back to What is VBA in Excel
Helpful Links – Developer Tab – Visual Basic Editor – Run a Macro – Personal Macro Workbook – Excel Macro Recorder