Get Today’s Date and Current Time Using VBA: Quick Guide for Excel Automation

In Excel, the versatility of VBA is highlighted by its ability to perform a variety of functions, one of which is retrieving the current date and time. I often find myself needing to timestamp data entries or trigger time-based actions within my worksheets. To do this effectively, I use VBA code to fetch the system’s date and time and then apply it within a cell or use it as part of more complex programming logic.

The process starts with leveraging the built-in VBA functions. For example, the ‘Date’ and ‘Now’ functions are straightforward tools that quickly deliver the date and time, respectively. I use variables to capture these values or directly output them into message boxes with ‘MsgBox’ to provide on-the-fly information to the user. Additionally, manipulating these date and time values, like extracting only the integer portion to get the date, demonstrates the flexibility and power that VBA macros offer to customize Excel’s behavior.

Today’s Date in VBA

Here’s how I grab today’s date in VBA. I use the Date function:

Sub myMacro()
    MsgBox Date
End Sub

This pops up a message box with the current date. To put the date in a spreadsheet cell, like A1, I do:

Sub myMacro()
    Range("A1") = Date
End Sub

Running the macro fills A1 with today’s date, displayed in the system’s date format which typically looks like mm/dd/yyyy or dd/mm/yyyy, depending on my regional settings.

Wanna see the function in action? Check out this . If you’re itching to try it out, here’s how you can run this code.

Remember, whether it’s inserting the date into a cell or showing it in a message, the Date function is my go-to for snagging the current day.

Today’s Date and Current Time

Sub myMacro()
    Range("A1") = Now ' Set cell A1 with the current date and time
End Sub

• NOW function in VBA:
  • Retrieves system’s current date and time
  • Useful for timestamping in Excel sheets

Today Function Alternatives:

• Date() gets only the current date
• Now covers both date and time

Output Example:

• Into cell A1, Now displays:
  • 

Current Time

To snag just the current time in Excel without the date, here’s a handy trick I use:

Sub myMacro()
    Range("A1") = Format(Now, "HH:MM Am/Pm")
End Sub

What’s going on here?

• Now Function: Grabs the time right this second.
• Format Function: Tells Excel, “Hey, just show me the hours, minutes, and whether it’s AM or PM.”

When this macro rocks and rolls, your cell A1 gets the current hour and minute, looking crisp like “10:25 AM”. Bingo!

Changing Today’s Date Format (Example: YYYYMMDD)

Here’s a quick way to change the date format to YYYYMMDD in Excel:

Sub myMacro()
    Range("A1").Value = Format(Date, "YYYY/MM/DD")
End Sub

• Use Format(Date, "YYYY/MM/DD") to set the format.
• The Value property inputs the formatted date into cell A1.