Range("A1").Value = Date writes today’s date, Range("A1").Value = Time the current time, and Range("A1").Value = Now both. The values come from the PC’s clock and stay fixed, unlike =TODAY() and =NOW(), which update whenever the sheet recalculates.
To control how the cell shows the value, set its number format and keep the real date underneath: Range("A1").NumberFormat = "yyyy-mm-dd" or "dd mmm yyyy h:mm AM/PM". The value still sorts and calculates as a date.
Use Format when you need text, such as a message or a file name: MsgBox Format(Now, "h:mm AM/PM") or "Report " & Format(Date, "yyyymmdd") & ".xlsx". In a format string, mm straight after h or hh means minutes and anywhere else means the month, so use nn for minutes when in doubt.
Writing a Format result into a cell doesn’t keep the layout. Range("A1").Value = Format(Date, "yyyy/mm/dd") is converted back to a date and shown in a default date format, and Format(Date, "yyyymmdd") becomes the number 20261019. Set NumberFormat instead.
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, as in the following example, it returns the current date according to the system’s date setting.
Sub myMacro()
Range("A1") = Date
End SubWhen 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 using the following code.
Sub myMacro()
MsgBox Date
End SubToday’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 SubCurrent Time
Now, if you want to get the current time only instead of date and time, you need to use another function, “Format”, 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 SubChanging 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 SubNote: In the above code, we have used the value property to enter the date into cell A1.
Related Tutorials
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.