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)

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 Date in Excel Formula

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: How to Insert Current Date in Excel

3. Format the 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”)

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:

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 Insert Dates in Excel Automatically

Things to Remember

• 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.

Download Practice Workbook

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

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

• Automatically Enter Date When Data Entered in Excel
• How to Auto Populate Date in Excel When Cell Is Updated
• How to Perform Automatic Date Change in Excel Using Formula
• How to Insert Day and Date in Excel

<< Go Back to Insert Date | Date-Time in Excel | Learn Excel