EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Date & Time Functions VBA DateDiff
Secondary Sidebar
VBA Date & Time Functions
  • VBA Date and Time
    • VBA Month
    • VBA DateSerial
    • VBA Date Format
    • VBA Time
    • VBA TIMER
    • VBA Date
    • VBA Format Number
    • VBA DateAdd
    • VBA DateValue
    • VBA DatePart
    • VBA DateDiff
    • VBA Format Date

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.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)

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
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

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

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

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

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more