EDUCBA

Home VBA VBA Resources VBA Date & Time Functions VBA DateDiff

VBA DateDiff

By Madhuri ThakurMadhuri Thakur

VBA DateDiff in Excel

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:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of DateDiff Function

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 1st  (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.

VBA DateDiff Example1-1

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

 VBA DateDiff Example1-2

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

VBA DateDiff Example1-3

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.

VBA DateDiff Example1-4

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.

VBA DateDiff Step 5

How to Use the DateDiff Function in Excel VBA?

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

You can download this VBA DateDiff Excel Template here – VBA DateDiff Excel Template

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

VBA DateDiff Example 1-6

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

VBA DateDiff Example 1-7

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

VBA DateDiff Example 1-8

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.

VBA DateDiff Example1-9

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

Year difference Example 2-1

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

Year difference Example 2-1-1

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

Month Difference Example 2-2

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

month difference Example 2-2-2

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

weeks difference Example 2-3

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

weeks difference Example 2-3-3

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

Quarter Difference Example2-4

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

Quarter Difference Example2-4-4

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

Days Difference Example 2-5

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

Days Difference Example 2-5-5

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

VBA DateDiff Example 2-6

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

VBA DateDiff Example 2-6-6

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

VBA DateDiff Example 2-7

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

VBA DateDiff Example 2-7-7

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

VBA DateDiff Example 2-8

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

VBA DateDiff Example 2-8-8

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

VBA DateDiff Example 2-9

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

VBA DateDiff Example 2-9-9

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

VBA DateDiff Example 2-10

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

VBA DateDiff Example 2-10-10

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

VBA DateDiff Example 2-11

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

VBA DateDiff Example 2-11-11-1

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

VBA DateDiff Example 2-12

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
0 Shares
Share
Tweet
Share