VBA Date Function (13 Uses of Macros with Examples)

Download Practice Workbook

Function 1 – DateAdd Function as Date in VBA

In VBA, we use the DateAdd function to add days in a particular date. This 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:

It added 15 dates in the current date in VBA.

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

Function 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:

It returns the difference between the two dates in VBA.

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

Function 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:

You can use this date function in this manner in the VBA codes.

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

Function 4 – DateSerial Function

You can set 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:

Function 5 – VBA DateValue Function

We use the DateValue function to define a date.

Syntax:

DateValue(date)

Argument:

The date argument is usually a string expression describing a date from January 1, 100, through December 31, 9999. Regardless, the date can be further than any expression that symbolize a date, a time or both 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:

Function 6 – 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:

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

Function 7 – 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 Concatenate in Excel VBA (4 Methods)

Function 8 – 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:

Function 9 – 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:

The VBA date function returns 4, which is Wednesday.

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

Function 10 – 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:

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

Function 11 – 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:

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

Function 12 – 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:

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

Function 13 – 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:

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.

Example 1 – Calculate Overdue Days using Date in VBA

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.

Steps

• Press Alt+F11 to open the VBA editor.
• Select Insert > Module.

• Add 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.

• Save the file.
• Press Alt+F8 to open the Macro dialog box.
• Select the macro to run
• Click on Run.

The overdue days will be displayed as shown below.

Similar Readings

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

Example 2 – Find Birth Year from Date using VBA

We will extract the birth year from the date and display the birth year of the last entry separately in a dialog box.

Steps

• Press Alt+F11 to open the VBA editor.
• Select Insert>Module.

• Add 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

• Save the file.
• Press Alt+F8 to open the Macro dialog box.
• Select find_year.
• Click on Run.

Example 3 – Add Days in Date using VBA

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.

The following sample dataset will be used for illustration.

We will add five more days to these given dates and generate a new date.

Steps

• Press Alt+F11.
• Select Insert>Module.

• Add 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

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

• Save the file.
• Press Alt+F8.
• Select the macro.
• Click on Run.

The days will be added into a date using the date in VBA. You can modify the code according to your choice.

Things to Remember

✎ VBA Date function works like the TODAY function.

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

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

✎ 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.

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)