VBA DateDiff

DateDiff Function in Excel VBA

VBA Datediff function provides the difference between two specified intervals. Here, the interval may be specified as hours/months/days… etc. as specified by the user. It is an inbuilt function in Excel and categorized as the “Date/Time” function. It can be used as a “VBA” function in Excel.

Syntax:

Parameters of DateDiff Function in Excel VBA

Parameter or Arguments used in DateDiff function.

1. Interval:

• It is mandatory.
• Type:- String type expression.
• It calculates the difference between the two dates.

Setting(interval)	Description/Explanation
“s”	Seconds
“n”	Minutes
“h”	Hours
“d”	Days
“w”	Weekday
“m”	Months
“ww”	Week
“y”	Day of the year
“q”	Quarter
“yyyy”	Year

2. Date1:

•  It is mandatory.
• Type:- Date.
• It represents the start date/time for the calculation of datediff.

3. Date2:

• It is mandatory.
• Type:- Date.
• It represents the end date/time for the calculation of datediff.

4. First day of the week:

• It is optional.
• Type:- Numeric or Text.
• It specifies the day that is used as the first day of the week.
• If this argument or parameter is omitted, it assumes Sunday(VbSunday) as the first day of the week.

5. First week of the year

• It is optional.
• Type:- Numeric or Text.
• It specifies the day that is used as the first week of the year.
• If this argument or parameter is omitted, it assumes January 1^st  (vbFirstJan1)as the first week of the year.

How to Enable Developer’s Tab in Excel?

The developer tab is mandatory on the Excel ribbon to start and write VBA macro. Below are the different steps to enable developers tab in excel VBA:

Step 1: Go to the File menu tab.

Step 2: In the File menu, click on Options situated at the last of the list available options under the menu.

 

Step 3: Click on Customize Ribbon to access the ribbon customization options.

Step 4: Here in the customization options, you can see the Developer option. Checkmark it, so that it gets activated on the main ribbon of excel and can easily be accessed. Click OK after checking the Developer option.

As soon as you hit OK, you can see the Developer tab active in the Excel ribbon menu with a bunch of different options available under. See the screenshot below.

How to Use the DateDiff Function in Excel VBA?

Below are the different steps to use the DateDiff Function in Excel VBA:

Example #1

In this example, follow the below steps to use DateDiff Function in VBA:

Step 1: First create a macro name.

Code:

Sub bb()

End Sub

Step 2: Two variables are defined as date and assigned date to them.

Code:

Sub bb()

Dim dt1 As Date
Dim dt2 As Date
dt1 = #1/1/2010 9:00:00 AM#
dt2 = #4/19/2019 11:00:00 AM#

End Sub

Step 3: Write the syntax of the Datediff function taking the required argument and assign the same through the VBA message box.

Code:

Sub bb()

Dim dt1 As Date
Dim dt2 As Date
dt1 = #1/1/2010 9:00:00 AM#
dt2 = #4/19/2019 11:00:00 AM#
MsgBox DateDiff("h", dt1, dt2)

End Sub

Step 4: Run the code by pressing the F5 key or by clicking on the Play button. So that the result will be displayed in the message box.

Example #2

In the below example, the datediff function calculates the number of years between the two dates “09/06/2016” and “16/12/2020”. Here, optional parameters are not taken into consideration.

Code:

Sub AA()

'Year difference
MsgBox DateDiff("yyyy", "09/06/2016", "16/12/2020")

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Example #3

In the below example, the datediff function calculates the number of months between the two dates ”09/06/2016” and “16/12/2020”. Here, optional parameters are not taken into consideration.

Code:

Sub AA1()

'month difference
MsgBox DateDiff("m", "09/06/2016", "16/12/2020")

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Example #4

In the below example, the datediff function calculates the number of weeks between in the two dates ”09/06/2016” and “16/12/2020”. Here, optional parameters are not taken into consideration.

Code:

Sub AA2()

'weeks difference
MsgBox DateDiff("ww", "09/06/2016", "16/12/2020")

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Example #5

In the below example, the “datediff” function calculates the number of quarters between the two dates” 09/06/2016” and “16/12/2020”. Here, optional parameters are not taken into consideration.

Code:

Sub AA3()

'quarter difference
MsgBox DateDiff("q", "09/06/2016", "16/12/2020")

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Example #6

In the below example, the “datediff” function calculates the number of days between the two dates” 09/06/2016” and “16/12/2020”. Here, optional parameters are not taken into consideration.

Code:

Sub AA4()

'days difference
MsgBox DateDiff("d", "09/06/2016", "16/12/2020")

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Example #7

In the below example, the “datediff”  function calculates the number of hours between the two dates and time ”9:00 on  01/01/ 2010” and “11:00 on 19/04/ 2019”.

Code:

Sub bb1()

'Calculate the numberof hours between 1/1/2010 9:00 and 19/4/2019 11:00
Dim dt1 As Date
Dim dt2 As Date
dt1 = #1/1/2010 9:00:00 AM#
dt2 = #4/19/2019 11:00:00 AM#
MsgBox DateDiff("h", dt1, dt2)

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Example #8

In the below example, the “datediff” function calculates the number of seconds between the two dates and time ”9:00 on  01/01/ 2010” and “11:00 on 19/04/ 2019”.

Code:

Sub bb2()

'Calculate the number of seconds between 1/1/2010 9:00 and 19/4/2019 11:00
Dim dt1 As Date
Dim dt2 As Date
dt1 = #1/1/2010 9:00:00 AM#
dt2 = #4/19/2019 11:00:00 AM#
MsgBox DateDiff("s", dt1, dt2)

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Example #9

In the below example, the “datediff” function calculates the number of minutes between the two dates and time ”9:00 on  01/01/ 2010” and “11:00 on 19/04/ 2019”.

Code:

Sub bb3()

'Calculate the number of minutes between 1/1/2010 9:00 and 19/4/2019 11:00
Dim dt1 As Date
Dim dt2 As Date
dt1 = #1/1/2010 9:00:00 AM#
dt2 = #4/19/2019 11:00:00 AM#
MsgBox DateDiff("n", dt1, dt2)

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Example #10

 If the argument is specified as “w”(weeks), the “Datediff” function returns the number of the whole week between the two dates. Partial weeks are ignored. In the example, the “DateDiff” function calculates the number of whole weeks between the dates 01/01/2010 and 19/4/2019.

Code:

Sub bb4()

'Calculate the number of weeks between 1/1/2010 and 19/4/2010
Dim dt1 As Date
Dim dt2 As Date
dt1 = #1/1/2010#
dt2 = #4/19/2010#
MsgBox DateDiff("w", dt1, dt2)

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Example #11

If the argument is specified as “ww”(calendar weeks), the “Datediff” function provides the number of weeks between the start of the week containing  Date1 and the start of the week containing Date2.

Code:

Sub bb5()

'Calculate the number of calendar weeks between 1/1/2010 and 19/4/2019
' First day of the week = Monday
Dim dt1 As Date
Dim dt2 As Date
dt1 = #1/1/2010#
dt2 = #4/19/2019#
MsgBox DateDiff("ww", dt1, dt2, vbMonday)

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Example #12

In the below example, “datediff” function is used for dates”1/1/1990” and  “1/1/1998”

Code:

Sub cc()

Dim dt1 As Date
Dim dt2 As Date
dt1 = #1/1/1990 9:00:00 AM#
dt2 = #1/11/1998 11:00:00 AM#
MsgBox ("line 1:" & DateDiff("h", dt1, dt2))
MsgBox ("line 2:" & DateDiff("s", dt1, dt2))
MsgBox ("line 3:" & DateDiff("n", dt1, dt2))
MsgBox ("line 4:" & DateDiff("d", dt1, dt2))
MsgBox ("line 5:" & DateDiff("m", dt1, dt2))
MsgBox ("line 6:" & DateDiff("q", dt1, dt2))
MsgBox ("line 7:" & DateDiff("w", dt1, dt2))
MsgBox ("line 8:" & DateDiff("ww", dt1, dt2))
MsgBox ("line 9:" & DateDiff("y", dt1, dt2))
MsgBox ("line 10:" & DateDiff("yyyy", dt1, dt2))

End Sub

To run the program press the “F8” or “Run” tab. The result will be displayed in the message box.

Then, click on “Ok” to get the next result.

Conclusion

“DateDiff” function thus helps in determining how many specified time intervals exist between two specified dates and times.

Recommended Articles

This is a guide to VBA DateDiff. Here we discuss how to use DateDiff function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Declare Array
2. VBA on Error
3. VBA Columns
4. VBA Environ