Rifat Hassan
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:
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 SubNote: 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
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 SubNote: 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
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:
=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 SubNote: 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:
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 SubNote: 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
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.
