VBA Date Function in Excel

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.

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.