The VBA Date function in Excel is a built-in function in MS Excel. It does not take any input arguments or parameters. It returns the current system date. It is a ‘Data and Time’ type function. Default format of the Date function is ‘mm/dd/yyyy’. The Date function plays a crucial major role. It allows you to retrieve the current system date and perform various operations related to date. Let us explore the syntax, arguments, return value, and different unique real-time examples to demonstrate the versatility of the VBA Date function.
The VBA Date function use in either procedure or function in a VBA editor window in Excel. We can use the VBA date function any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the date function, where we can use this VBA date function and real-time examples.
- Objective
- Syntax of VBA Date Function
- Parameters and Return Value
- Where we can apply or use the Date Function?
- Display current system date on the screen
- Display current system date on the Worksheet
- Formatting Date
- Date Comparison
- Adding Days to Date
- Extracting Year, Month, and Day
- Checking for Leap Year
- Date Conversion
- Determining Weekday
- Determine if a Date is a Weekend
- Age Calculation
- Date Validation
- Calculate Days Between Two Dates
- Add a Specific Number of Days to a Date
- Calculate the First Day of the Month
- Check if a Date is in the Past
- Calculate Quarter of the Year
- Download File
- Instructions to Run VBA Macro Code
- Other Useful Resources
-
Examples:
Syntax of VBA Date Function:
The syntax of the VBA Date function is
Date()
In the above syntax parentheses is optional. If there is no argument, then no need to specify parentheses.
Parameters and Return Value
There are no parameters or arguments for the Date function.
Return Value:
The Date function returns the current system date as a ‘Date’ data type.
Where we can apply or use the Date Function?
We can use this VBA date function in MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.
1. Display current system date on the screen
Here is a simple example of the VBA Date function. This below example macro uses the Date function and displays the current system date.
Sub VBAF1_Date_Function_Ex1() 'Display Current System Date on the screen 'Variable declaration Dim sCurrentDate As Date 'Assign current system date to variable sCurrentDate = Date MsgBox sCurrentDate, vbInformation, "Current System Date" End Sub
In the above example ‘sCurrentDate’ declared as a Date data type. This variable ‘sCurrentDate’ now contains the current system date.
Output: Here is the screen shot of first example output.
2. Display current system date on the Worksheet
Here is another example of the VBA Date function. This below example macro uses the Date function and displays the current system date on the Worksheet named ‘VBAF1.Com’ in Range ‘B18’.
Sub VBAF1_Date_Function_Ex2() 'Display Current System Date on the Worksheet 'Variable declaration Dim sCurrentDate As Date 'Assign current system date to variable sCurrentDate = Date Sheets("Sheet1").Range("B18") = sCurrentDate 'Or 'Sheets("Sheet1").Cells(18, 2) = sCurrentDate End Sub
Output:
Here is the screen shot of second example output.
3. Formatting Date
Here is one more example with VBA Date function. This below example macro uses the Date function and changes the format of the date. Finally, it displays the current system date on the screen.
Sub VBAF1_Date_Function_Ex3() 'Format and Display Current System Date on the screen 'Variable declaration Dim sCurrentDate As Date 'Assign current system date to a variable sCurrentDate = Date MsgBox Format(sCurrentDate, "DD/MMMM/YYYY"), vbInformation, "Current System Date" End Sub
Output: Here is the screen shot of third example output.
4. Date Comparison
The below example demonstrates how the Date function can be used to compare two dates. If the current date is earlier than the specified futureDate (December 31, 2025), a message box is displayed.
Sub VBAF1_DateComparison() 'Variable Declaration Dim dfutureDate As Date Dim dCurrentDate As Date 'Assign future date to variable dfutureDate = #12/31/2025# dCurrentDate = #6/16/2023# 'Macro Running Date to compare output 'Check current date with future date If dCurrentDate < dfutureDate Then MsgBox "The current date " & dCurrentDate & " is earlier than " & dfutureDate & ".", vbInformation, "VBAF1.COM" End If End Sub
Output: 31-12-2025
Message box is displaying as shown in the below screen shot.
5. Adding Days to Date
The Date function, combined with arithmetic addition operation, allows us to perform date calculations. In this case, 7 days are added to the current date, resulting in a future date of June 23, 2023.
Sub VBAF1_AddingDaysToDate() 'Variable Declaration Dim dfutureDate As Date 'Date: 16th June 2023 while running macro for your reference to check output dfutureDate = Date + 7 'Date:16th June 2023 'Display Message MsgBox "The current date is " & Date & " and the future date after adding 7days is : " & dfutureDate, vbInformation, "VBAF1.COM" End Sub
Output: 23-06-2023
6. Extracting Year, Month, and Day
By using the Year, Month, and Day functions in combination with the Date function, we can extract specific components like year, month, and date of the current date .
Sub VBAF1_ExtractYearMonthDay_FromDate() 'Variable Declaration Dim currentYear As Integer, currentMonth As Integer, currentDay As Integer 'Get Current Year currentYear = Year(Date) 'Get Current Month currentMonth = Month(Date) 'Get Current Day currentDay = Day(Date) 'Display Message MsgBox "Current Year is : " & currentYear & vbNewLine & "Current Month is : " & currentMonth & vbNewLine & "Current Day is : " & currentDay, vbInformation, "VBAF1.COM" End Sub
Output:
Current Year: 2023
Current Month: 6
Current Day: 16
7. Checking for Leap Year
The Date function can be used to determine if the current year is a leap year. The Boolean variable isLeapYear is set to True if the year is a leap year otherwise, it is set to False.
Sub VBAF1_Checking_for_LeapYear() 'Variable Declaration Dim isLeapYear As Boolean 'Year :2023 while running macro for your reference to check output 'Check for leap year isLeapYear = (Year(Date) Mod 4 = 0 And (Year(Date) Mod 100 <> 0 Or Year(Date) Mod 400 = 0)) 'Display message MsgBox "Is " & Year(Date) & " Leap Year : " & isLeapYear, vbInformation, "VBAF1.COM" End Sub
Output: False
8. Date Conversion
Here, the CDate function is used to convert a string representation of a date ("2023-06-16") into a 'Date' data type, changes as a date.
Sub VBAF1_DateConversion() 'Variable Declaration Dim dateString As String Dim convertedDate As Date 'Specify date in string format dateString = "2023-06-16" 'Convert string to date convertedDate = CDate(dateString) 'Display message MsgBox "Date is : " & convertedDate, vbInformation, "VBAF1.COM" End Sub
Output:16-6-2023
9. Determining Weekday
The Weekday function is used to determine the weekday of the current date. In this case, the function returns 6, indicating that it is a Friday (assuming Sunday is represented as 1).
Sub VBAF1_DeterminingWeekday() 'Variable Declaration Dim weekdayNumber As Integer 'Date: 16th June 2023 while running macro for your reference to check output 'Check for the Week day weekdayNumber = Weekday(Date) 'Display message MsgBox "Week day : " & weekdayNumber, vbInformation, "VBAF1.COM" End Sub
Output: 6
10. Determine if a Date is a Weekend
The Weekday function returns a value representing the day of the week (1 for Sunday, 2 for Monday, etc.). By checking if the value is 1 or 7, we can determine if it's a weekend.
Sub VBAF1_Determine_if_Date_is_Weekend() 'Variable Declaration Dim checkDate As Date 'Assign date to variable checkDate = Date 'Date: 16th June 2023 while running macro for your reference to check output 'Check and Display message If Weekday(checkDate) = 1 Or Weekday(checkDate) = 7 Then MsgBox "It's a weekend!", vbInformation, "VBAF1.COM" Else MsgBox "It's not a weekend.", vbInformation, "VBAF1.COM" End If End Sub
Output: A message box will display whether the current date is a weekend or not as shown in the below screenshot.
11. Age Calculation
The DateDiff function calculates the difference in years between the birth Date (January 1, 1980) and the current date using VBA DATE Function in Excel. In this case, it returns the age of 43.
Sub VBAF1_AgeCalculation() 'Variable Declaration Dim birthDate As Date Dim age As Long 'Enter Birth date birthDate = #1/1/1980# 'Age calculation age = DateDiff("yyyy", birthDate, Date) 'Display message MsgBox "Age is :" & age, vbInformation, "VBAF1.COM" End Sub
Output: 43
12. Date Validation
The below example showcases how the IsDate function, in combination with the Date function, can be used to validate user-entered dates. If the input is a valid date, a message box confirms its validity. Otherwise, it displays an error message.
Sub VBAF1_DateValidation() 'Variable Declaration Dim userInput As String 'Enter Date in 'mm/dd/yyyy' format userInput = InputBox("Enter a date (mm/dd/yyyy):") 'Display message If IsDate(userInput) Then MsgBox "Valid date." Else MsgBox "Invalid date." End If End Sub
Output: A message box displaying "Valid date" or "Invalid date" based on user input.
13. Calculate Days Between Two Dates
The below example calculate Days Between Two Dates. By subtracting the start date from the end date, we can determine the number of days between the two dates.
Sub VBAF1_CalculateDays_Between_Two_Dates() 'Variable Declaration Dim startDate As Date Dim endDate As Date Dim daysDifference As Integer 'Assign start and end date to variable startDate = #1/1/2023# endDate = #6/16/2023# 'Difference of two dates daysDifference = endDate - startDate 'Display message MsgBox "Number of days between start and end date is: " & daysDifference, vbInformation, "VBAF1.COM" End Sub
Output: 166
14. Add a Specific Number of Days to a Date
Let us see an example how to add a specific number of days to a date. We can obtain a future date, by adding a number of days to the current date,
Sub VBAF1_Add_SpecificNumber_Days_to_Date() 'Variable Declaration Dim futureDate As Date 'Date: 16th June 2023 while running macro for your reference to check output 'Adding 8 days to the current date futureDate = Date + 8 'Display message MsgBox "Adding 8 Days to the date - 16th June 2023 : " & futureDate, vbInformation, "VBAF1.COM" End Sub
Output: A message box will display the date that is 8 days ahead of the current date.
15. Calculate the First Day of the Month
The DateSerial function is used to create a date with the current year, month, and 1 as the day value, effectively retrieving the first day of the month.
Sub VBAF1_Calculate_FirstDay_ofMonth() 'Variable Declaration Dim firstDay As Date 'Date: 19th June 2023 while running macro for your reference to check output 'First day of the month calculation firstDay = DateSerial(Year(Date), Month(Date), 1) 'Display message MsgBox "First Day of the Month: " & firstDay, vbInformation, "VBAF1.COM" End Sub
Output: A message box will display the first day of the current month.
16. Check if a Date is in the Past
The below example checks whether the current date is in the past or not using VBA DATE Function in Excel. We can determine if it is in the past, by comparing the current date with itself.
Sub VBAF1_Check_if_Date_is_inPast() 'Variable Declaration Dim checkDate As Date 'Assign start and end date to variable dDate = #1/1/2023# 'Date: 19th June 2023 while running macro for your reference to check output 'Assign date to variable checkDate = Date 'Check and display message If checkDate < Date Then MsgBox "The date is in the past.", vbInformation, "VBAF1.COM" Else MsgBox "The date is not in the past.", vbInformation, "VBAF1.COM" End If End Sub
Output: A message box will display whether the current date is in the past or not.
17. Calculate Quarter of the Year
In this example we are calculating the Quarter of the Year using VBA Date function. By dividing the month number by 3, subtracting 1, and taking the integer value, we can calculate the current quarter.
Sub VBAF1_Calculate_Quarter_theYear() 'Variable Declaration Dim quarter As Integer 'Date: 16th June 2023 while running macro for your reference to check output 'Quarter Calculation quarter = Int((Month(Date) - 1) / 3) + 1 'Display message MsgBox "Current Quarter: " & quarter, vbInformation, "VBAF1.COM" End Sub
Output:A message box will display the quarter of the year based on the current date.
Download File
Click on following link to download free example excel workbook to learn more about VBA Now function.
Watch at YouTube:
You can also watch in youtube.
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables Blog
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Pivot Table Tutorials