How to Get the Current Date in VBA (3 Ways)

In this article, I’ll show you how you can get the current date in VBA in Excel. You’ll learn to show the current date, show the current time, as well as to format the date and time in your desired format.

How to Get the Current Date in VBA (Quick View)

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

3 Ways to Get the Current Date in VBA 

Let’s explore the best ways to get the current date and time in a Macro in VBA.

1. Get Current Date by the Date Function of VBA

First of all, let’s see how we can get the current date. You can get the current date in VBA quite comprehensively using the Date function of VBA.

The line of code will be:

The complete code to display the current date will be:

⧭ VBA Code:

Sub Get_Current_Date()

Current_Date = Date

MsgBox Current_Date

End Sub

Note: This code creates a Macro called Get_Current_Date.

⧭ Output:

Run this Macro, and you’ll get a Message Box displaying the current date, 11-Jan-22.

Read more: How to Insert Current Date in Excel (4 Suitable Examples)

2. Insert Current Date and Time by the Now Function of VBA

You can use the Now function of VBA to get the current date along with the current time.

The line of code will be:

Therefore, the complete code to display the current date and time will be:

⧭ VBA Code:

Sub Get_Current_Date_and_Time()

Current_Date_and_Time = Now()

MsgBox Current_Date_and_Time

End Sub

Note: This code creates a Macro called Get_Current_Date_and_Time.

⧭ Output:

Run this Macro, and you’ll get a Message Box displaying the current date and time, 11-Jan-22 11:23:20 AM.

Read More: Now and Format Functions in Excel VBA (4 Examples)

Similar Readings

• Date Variable in VBA Codes (7 Uses of Macros with Examples)
• How to Use Excel Date Shortcut
• Calculate Due Date with Formula in Excel (7 Ways)
• How to Use IF Formula with Dates (6 Easy Examples)

3. Format Current Date and Time by the Format Function of VBA

Up till now, we’ve learned to get the current date and time. This time, let’s see how we can display the date and time in our desired format.

3.1 Format Current Date

First, we’ll format the current date only.

We’ll use the Format function of VBA for this purpose. The syntax of the function is:

Therefore, to display the current date in the format dd/mm/yyyy, the line of code will be:

Current_Date = Format(Date, “dd/mm/yyyy”)

And the complete VBA code will be:

⧭ VBA Code:

Sub Format_Date_and_Time()

Current_Date = Format(Date, "dd/mm/yyyy")

MsgBox Current_Date

End Sub

Note: This code creates a Macro called Format_Date_and_Time.

⧭ Output:

If you run this code, it’ll show the current date in your desired format, dd/mm/yyyy, 11/01/2022.

3.2 Format Current Date and Time

You can also use the Format function to format the current date and the current time together.

Let’s display the current date and time in the format dd/mm/yyyy hh:mm:ss am/pm.

The line of code will be:

And the complete VBA code will be:

⧭ VBA Code:

Sub Format_Date_and_Time()

Current_Date_and_Time = Format(Now(), "dd/mm/yyyy hh:mm:ss am/pm")

MsgBox Current_Date_and_Time

End Sub

Note: This code creates a Macro called Format_Date_and_Time.

⧭ Output:

If you run this code, it’ll show the current date and time in your desired format, dd/mm/yyyy hh:mm:ss am/pm, 11/01/2022 12:03:45 pm.

Read more: How to Format Date with VBA in Excel (4 Methods)

Summary

• The NOW function of Visual Basic Application returns the current date and time.
• The Date function returns the current date.
• The Format function displays a date and time in any desired format.

Conclusion

Using these methods, you can get and display the current date and time in a Macro in Excel. Do you have any questions? Feel free to ask us.

Further Readings

• Excel VBA to Find Week Number (6 Quick Examples)
• How to Use Year Function in Excel VBA (5 Suitable Examples)
• VBA Date to String Conversion in Excel (6 Methods)
• Use the Day Function in Excel VBA (3 Examples)
• How to Get the Day of Week Using VBA (4 Suitable Methods)
• Excel Date Picker for Entire Column
• How to Use VBA DateSerial Function in Excel (5 Easy Applications)