VBA Date Function (12 Uses of Macros with Examples)

In Microsoft Visual Basic Application, date functions or any date-related concepts are essential to perform various operations in your dataset. You may find yourself in various situations where you will have to use these functions. In this tutorial, you will learn about the Date function of VBA with suitable examples and proper illustrations. Also, we will provide you with multiple date functions that you can implement in your worksheet.

Download Practice Workbook

Introduction to VBA Date Function

Excel categorizes Dates in the Date/Time function. It is a built-in function. We can use it in VBA macros to perform any date-related operations.

Before you start with the Date function, you have to know about the Date variables in VBA.

⏺ Syntax

⏺ Arguments Explanations

There are no arguments.

⏺ Returns

Returns the current date.

⏺ Available in

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

⏺ Example

As you know, there are no arguments you can only just input it like the following:

Sub vba_date()

Dim current_date As Date
current_date = Date
MsgBox current_date

End Sub

When we work with the Date function, we don’t give any parenthesis in the VBA codes like this example.

Output:

12 Date Functions in VBA You Should Learn

Now, in the upcoming sections, we will show you some additional date functions that you can use for numerous purposes in VBA. Read these sections to know every possible way to work with the date in VBA. We suggest you keep them in your arsenal. It will surely improve your knowledge.

1. DateAdd Function as Date in VBA

In VBA, we use the DateAdd function to add days in a particular date. After that, it will return the resulting date.

Syntax:

DateAdd(interval, number, date)

Arguments:

interval: It is Required. String expression is the interval of time you want to add.

number: Required. It is a Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).

date: The original date/time.

Code Snippet:

Sub dateadd_function()

Dim result_Date As Date
result_Date = DateAdd("d", 15, "1/31/2022")
MsgBox result_Date

End Sub

Output:

As you can see, it added 15 dates in the current date in VBA.

Read More: How to Use IsDate Function in VBA (3 Examples)

2. DateDiff Function in VBA

The DateDiff function returns a Variant (Long) determining the number of time gaps between two specified dates.

Syntax:

DateDiff(interval, date1, date2, [ firstdayofweek, [ firstweekofyear ]] )

Arguments:

interval: It is Required. String expression is the interval of time you want to addString expression that is the gap of time you use to compute the distinction between two dates.

date1,date2 : Required; Variant (Date). Two dates you want to use in the calculation.

firstdayofweek: Optional. A constant that defines the first day of the week. If not fixed, Sunday is assumed.

firstweekofyear: Optional. A constant that establishes the first week of the year. If not set, the first week is supposed to be the week in which January 1 appears.

Code Snippet:

Sub DateDiff_Function()

Dim result As Long
result = DateDiff("d", "1/31/2022", "2/12/2022")
MsgBox result

End Sub

Output:

In the end, it returns the difference between the two dates in VBA.

Read More: How to Use VBA DateDiff Function in Excel (9 Examples)

3. DatePart Function as Date

The DatePart function returns a Variant (Integer) containing the defined part of a provided date.

Syntax:

DatePart(interval, date, [ firstdayofweek, [ firstweekofyear ]])

Arguments:

interval: It is Required. String expression is the interval of time you want to addString expression that is the gap of time you use to compute the distinction between two dates.

date: Required; Variant (Date). The Date you want to use in the calculation.

firstdayofweek: Optional. A constant that defines the first day of the week. If not fixed, Sunday is assumed.

firstweekofyear: Optional. A constant that establishes the first week of the year. If not set, the first week is supposed to be the week in which January 1 appears.

Code Snippet:

Sub DatePart_Function()

Dim result As Integer
result = DatePart("m", "10/11/2022")
MsgBox result

End Sub

Output:

In this way, you can use this date function in VBA codes.

Read More: How to Use VBA FileDateTime Function in Excel (3 Uses)

4. DateSerial Function

You can see a date based on an input year, month, and day using The DateSerial function.

Syntax:

DateSerial(year, month, day)

Arguments:

year – Required field. A number between 100 and 9999, inclusive, or a numeric expression that symbolizes the year.

month – Required field. An integer value that defines the month.

day – Required field. An integer value that describes the day.

Code Snippet:

Sub date_serial()

Dim date_special As Date
date_special = DateSerial(2022, 1, 11)
MsgBox date_special

End Sub

Output:

Read More: How to Use VBA TimeSerial in Excel (3 Examples)

4. VBA DateValue Function

Now, we use the DateValue function to define a date.

Syntax:

DateValue(date)

Argument:

Here, the date argument is usually a string expression describing a date from January 1, 100, through December 31, 9999. Regardless, the date can furthermore any expression that can symbolize a date, a time, or both a date and time in that range.

Code Snippet:

Sub Date_value()

Dim result As Date
result = DateValue("January, 10, 2022")
MsgBox result

End Sub

Output:

As you can see, we successfully used the date function in VBA codes.

5. Day Function in VBA

It returns a Variant (Integer) determining a real number between 1 and 31, inclusive, conveying the day of the month.

Syntax:

Day(date)

Argument:

The required date argument is any Variant, numeric expression, string expression, or any combination. It represents a date. If the date includes Null, it will also return Null.

Code Snippet:

Sub day_function()

Dim date1, the_day
date1 = #12/12/2023#
the_day = Day(date1)
MsgBox the_day

End Sub

Output:

As you can see, using the VBA codes we found that the day of the given date is 12.

Related Content: VBA Format Function in Excel (8 Uses with Examples)

6. VBA Month Function as Date

It returns a Variant (Integer) defining a real number between 1 and 12, inclusive, conveying the month of the year.

Syntax:

Month(date)

Argument:

The required date argument is any Variant, numeric expression, string expression, or any combination. It represents a date. If the date includes Null, it will also return Null.

Code Snippet:

Sub month_function()

Dim date1, the_month
date1 = #12/12/2023#
the_month = Month(date1)
MsgBox the_month

End Sub

Output:

Related Content: How to Use VBA Right Function in Excel (6 Examples)

Similar Readings

• How to Use the VBA Environ Function (4 Examples)
• How to Use VBA And Function in Excel (4 Examples)
• VBA If – Then – Else Statement in Excel (4 Examples)
• How to Use VBA Abs Function in Excel (9 Examples)
• How to Use Concatenate in Excel VBA (4 Methods)

7. MonthName Function

It returns a string demonstrating the specified month.

Syntax:

MonthName(month, [ abbreviate ])

Argument:

month: It is Required. The numeric title of the month. For instance, January is 1, February is 2, and so on.

abbreviate: It is Optional. Boolean value that demonstrates if the month name is to be abbreviated. If skipped, the default is False, which indicates that the month name is not abbreviated.

Code Snippet:

Sub MonthName_Function()

Dim month_name As String
month_name = MonthName(9, True)
MsgBox month_name

End Sub

Output:

As you can see, we found the month name by this VBA Date Function.

8. Weekday Function

It returns a Variant (Integer) possessing a real number expressing the day of the week.

Syntax:

Weekday(date, [ firstdayofweek ])

Argument:

date: The required date argument is any Variant, numeric expression, string expression, or any combination. It represents a date. If the date includes Null, it will also return Null.

firstdayofweek: Optional. A constant that defines the first day of the week. If not fixed, Sunday is assumed.

Code Snippet:

Sub Weekday_Function()

Dim week_day As Integer
week_day = Weekday("4/27/2022")
MsgBox week_day

End Sub

Output:

As you can see, the VBA date function returns 4. That means Wednesday.

Related Content: How to Use Fix Function in Excel VBA (4 Examples)

9. VBA WeekdayName Function

Returns a string displaying the confined day of the week.

Syntax:

WeekdayName(weekday, abbreviate, firstdayofweek)

Argument:

weekday: Required field. The numeric identification for the day of the week. The numeric value of each day relies on the setting of the firstdayofweek setting.

abbreviate: This is Optional. Boolean value that implies if the weekday name is to be shortened. If skipped, the default is False, which signifies that the weekday name is not abbreviated or shortened.

firstdayofweek: Optional field. Numeric value that indicates the first day of the week. It can have various values.

Code Snippet:

Sub WeekdayName_Function()

Dim weekday_name As String
weekday_name = WeekdayName(6)
MsgBox weekday_name

End Sub

Output:

As you can see, the above VBA codes show the weekday name.

Related Content: How to Use VBA WeekdayName Function in Excel (2 Examples)

10. Year Function in VBA Date

It returns a Variant (Integer) having a real number expressing the year.

Syntax:

Year(date)

Argument:

The required date argument is any Variant, numeric expression, string expression, or any combination. It represents a date. If the date includes Null, it will also return Null.

Code Snippet:

Sub year_function()

Dim date1, the_year
date1 = #12/12/2023#
the_year = Year(date1)
MsgBox the_year

End Sub

Output:

Here you can see the year of the given date after implementing the VBA code.

Read More: How to Use VBA Replace Function in Excel (11 Applications)

11. FormatDateTime Function

This function returns an expression formatted as a date or time.

Syntax:

FormatDateTime(Date, [ NamedFormat ])

Argument:

Date: Required field. Date expression to be formatted.

NamedFormat: This is Optional. It is a numeric value that displays the date/time format. If omitted, it used the vbGeneralDate.

Code Snippet:

Sub FormatDateTime_Function()

d = ("2022-02-03 18:25")
MsgBox ("Format 1 : " & FormatDateTime(d))
MsgBox ("Format 2 : " & FormatDateTime(d, 1))
MsgBox ("Format 3 : " & FormatDateTime(d, 2))
MsgBox ("Format 4 : " & FormatDateTime(d, 3))
MsgBox ("Format 5 : " & FormatDateTime(d, 4))

End Sub

Output:

You will see the following dialog boxes after running the code:

Here, you can see all the time and date formats in VBA.

Read More: How to Use VBA TimeValue Function (6 Relevant Examples)

12. VBA CDate Function

The Function converts a valid date and time expression to a typical date.

Syntax:

CDate(date)

Argument:

The required date argument is any Variant, numeric expression, string expression, or any combination. It represents a date. If the date includes Null, it will also return Null.

Code Snippet:

Sub Cdate_Function()

Dim date1 As Variant
Dim date2 As Variant
date1 = CDate("Mar 11 2022")
date2 = CDate("29 Sep 2023")
MsgBox ("First date : " & date1 & vbCrLf & "Second date : " & date2)

End Sub

Output:

As you can see, our VBA code just returned a typical date format of Excel.

Related Content: How to Use VBA DIR Function in Excel (7 Examples)

Examples of VBA Date

In the following sections, we will provide you with three practical and suitable examples of date using VBA. These examples will contain date-related problems and their solutions. We recommend you read and practice all these examples to improve your VBA knowledge. Let’s get into it.

1. Calculate Overdue Days using Date in VBA

The meaning of overdue is happening late, or past the deadline. Late, particularly, past a deadline or too late to meet a requirement.

Suppose you have to submit an assignment due Sunday. But it is Tuesday, and you haven’t submitted it. You can call it two overdue days.

Take a look at the following dataset:

Here, we have a dataset of some students and their assignment submission date. You can see the last date of submission. Our goal is to find the overdue date based on the submission date. Now, to accomplish this, follow these steps:

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert > Module.

• After that, type the following code:

Sub overdue_days()

Dim cell As Integer
Dim J As Integer
Dim due_date As Date

due_date = #1/11/2022#

For cell = 5 To 11
If Cells(cell, 4).Value = due_date Then
 Cells(cell, 5).Value = "Submitted Today"
  ElseIf Cells(cell, 4).Value > due_date Then
  J  = due_date - Cells(cell, 4).Value
   J = Abs(J)
   Cells(cell, 5).Value = J & " Overdue Days"
  Else
   Cells(cell, 5).Value = "No Overdue"
 End If
Next cell

End Sub

We used the ABS function to remove the minus sign.

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select
• Then, click on Run.

As you can see, we have successfully used the date in VBA and found the overdue days.

Similar Readings

• How to Use VBA Space Function in Excel (3 Examples)
• Use VBA ChDir Function in Excel (4 Suitable Examples)
• How to Use IsNull Function in Excel VBA (5 Examples)
• Use VBA While Wend Statement in Excel (4 Examples)
• How to Call a Sub in VBA in Excel (4 Examples)

2. Find Birth Year from Date using VBA

Now, you can find the Year from a particular date. This is pretty simple to find.

Take a look at the following dataset:

Here, you can see the date of birth of some persons. Our goal is to extract the birth year from the date and also the birth year of the last entry Elizabeth.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert>Module.

• After that, type the following code:

Sub find_year()

Dim last_entry As Date
Dim cell As Integer

For cell = 5 To 11
 Cells(cell, 4).Value = Year(Cells(cell, 3).Value)
 If cell = 11 Then
  last_entry = Cells(cell, 3).Value
 End If
Next cell
MsgBox "Birth Year of last entry: " & Year(last_entry)

End Sub

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select find_year.
• Then, click on Run.

Finally, you can see we are successful in extracting the birth year from each date. Also, we found the birth year of the last entry using the date of VBA in Excel.

3. Add Days in Date using VBA

Now, you can define the date variable and use it to add the date. To perform this, we are using the DateAdd method of VBA. You can use this function to add days, months, and years to a particular date.

Take a look at the following dataset:

Here, you can see some names and some dates attached to them. Now, our goal is to add five more days to these given dates and generate a new date.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert>Module.

• After that, type the following code:

Sub add_days()

Dim first_date As Date
Dim second_date As Date
Dim cell As Integer

For cell = 5 To 11
  first_date = Cells(cell, 3).Value
  second_date = DateAdd("d", 5, first_date)
  Cells(cell, 4).Value = second_date
Next cell

End Sub

Here, we used “d” as an argument in the DateAdd function. You can change it to “y” or “m” to add years or months respectively,

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select.
• Then, click on Run.

As you can see, we have successfully added days into a date using the date in VBA. Now, you can modify the code according to your choice.

💬 Things to Remember

✎ VBA Date function actually works like the TODAY function in Excel.

✎ VBA DATE is a non-volatile function in excel. That means it will retain the data even there is a break in the power supply.

✎ Basically, VBA saves Date values as DATE at the time of implementation.

✎ So, if you try to assign a date variable as a string/text, it will cause an error.

✎ The default value of Date is 0:00:00 (midnight) on January 1, 0001.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the Date in VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

Related Articles

• Excel VBA ASC() Function – Get ASCII Value of Character
• How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA
• Use TRIM Function in VBA in Excel (Definition + VBA Code)
• How to Use VBA SPLIT Function in Excel (5 Examples)
• Use LCase Function in VBA in Excel (With 4 Examples)
• How to Use Log Function in Excel VBA (5 Suitable Examples)