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)

VBA Code to Get the Current Date in VBA


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:

Current_Date=Date()

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.

VBA Code to Get the Current Date in VBA

Output:

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

Output to Get the Current Date in VBA

Read more: How to Insert Current Date in Excel


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:

Current_Date_and_Time = Now()

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.

Output to Get the Current Date in VBA

Read more: Now and Format Functions in Excel VBA


Similar Readings


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:

=Format(Date,Format)

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.

VBA Code to Format the Current Date in VBA

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:

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

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.

VBA Code to Format Current Date in VBA

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


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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo