WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA DateDiff Function

VBA DateDiff Function

DATEDIFF function in VBA is an inbuilt function in VBA which is also categorized under date and time function in VBA, this function is used to get the difference between two dates, this function takes three arguments the first argument is what part of the difference we want which can be year days or months or seconds and two dates and the result is an integer.

VBA DateDiff

DATEDIFF Function in VBA

DATEDIFF Function in VBA calculates the difference between two dates in days, months, quarters, and years.

In excel, finding the difference between two dates has many ways. You don’t need a special formula to calculate the difference between two dates.

For example, look at the below image.

datediff Example 1

If we want to calculate the difference between these two dates, we can simply subtract the date 1 from date 2.

datediff Example 1-1

It has given us the difference between two dates in a number of days. This is the problem with this generic formula. If we need the difference in months, years, quarters, etc.… it cannot give.

In this article, we will show you how to use this DateDiff function in VBA.

What is the DATEDIFF Function in Excel VBA?

DATEDIFF in VBA stands for “Date Difference between two dates.”

This function can give us the number of the time interval between two dates. When we want to find the difference between two dates, we can find it in days, weeks, months, quarters, etc..

To understand the function, look at the below syntax of the function.

DateDiff

Interval: This nothing but in what way you want to calculate the date difference. Whether it is in days, months, weeks, quarters, etc.… Below is the list of the same.

Datediff 1

Date 1: What is the first date you want to find the difference.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Date 2: What is the second date you want to find the difference from Date 1.Date 1: What is the first date you want to find the difference.

Here the formula is Date 2 – Date 1.

[First Day of Week]: What is the first day of the week? We can agree with the following arguments.

datediff 2

[First Week Of the Year]: What is the first week of the year. We can enter the following arguments.

datediff 3

Examples of DATEDIFF Function in Excel VBA

The following are the examples of excel VBA DateDiff.

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

Example #1 – To Find Differences in Days

Assume you have two dates, “15-01-2018” and “15-01-2019”. Let’s find all kinds of differences between these two dates.

Step 1: Create a macro name first.

Code:

Sub DateDiff_Example1()

End Sub

VBA DateDiff Example 1
Step 2: Define Two Variables as Date.

Code:

Sub DateDiff_Example1()

   Dim Date1 As Date
   Dim Date2 As Date

End Sub

VBA DateDiff Example 1-1

Step 3: Now, for the Date1 variable, assign “15-01-2018” and for the Date2 variable, assign “15-01-2019”.

Code:

Sub DateDiff_Example1()

   Dim Date1 As Date
   Dim Date2 As Date

   Date1 = "15-01-2018"
   Date2 = "15-01-2019"

End Sub

VBA DateDiff Example 1-2

Step 4: Now, to store results, define one more variable as Long.

Code:

Sub DateDiff_Example1()

   Dim Date1 As Date
   Dim Date2 As Date
   
   Dim Result As Long

   Date1 = "15-01-2018"
   Date2 = "15-01-2019"
End Sub

VBA DateDiff Example 1-3

Step 5: Now assign the value for this variable through the DATEDIFF function in VBA

Code:

Sub DateDiff_Example1()

   Dim Date1 As Date
   Dim Date2 As Date

   Dim Result As Long

   Date1 = "15-01-2018"
   Date2 = "15-01-2019"

Result =DateDiff(

End Sub

VBA DateDiff Example 1-4

Step 6: The first argument is what kind of difference we need between these two dates. Let’s say we need to find the number of days, so supply the argument as “D.”

Code:

Sub DateDiff_Example1()
    
    Dim Date1 As Date
    Dim Date2 As Date

    Dim Result As Long

   Date1 = "15-01-2018"
   Date2 = "15-01-2019"

   Result =DateDiff("D",

End Sub

VBA DateDiff Example 1-5

Step 7: What is the first date to find the difference. Our first date is “15-01-2018” that we have already assigned to the variable “Date1”. So supply the variable name here.

Code:

Sub DateDiff_Example1()

    Dim Date1 As Date
    Dim Date2 As Date

    Dim Result As Long

    Date1 = "15-01-2018"
    Date2 = "15-01-2019"

    Result =DateDiff("D",Date1,

End Sub

VBA DateDiff Example 1-6

Step 8: What is the second date to find the difference. The second date is “15-01-2019,” which holds the value through variable “Date2”.

Code:

Sub DateDiff_Example1()

    Dim Date1 As Date
    Dim Date2 As Date

    Dim Result As Long

    Date1 = "15-01-2018"
    Date2 = "15-01-2019"

    Result = DateDiff("D", Date1, Date2)

End Sub

VBA DateDiff Example 1-7

Step 9: Ignore the last two parameters. Now assign the value of variable “Result” through the VBA message box.

Code:

Sub DateDiff_Example1()

    Dim Date1 As Date
    Dim Date2 As Date
    Dim Result As Long

    Date1 = "15-01-2018"
    Date2 = "15-01-2019"

    Result = DateDiff("D", Date1, Date2)

    MsgBox Result

End Sub

VBA DateDiff Example 1-8

Now run the code using the F5 key or manually, we will get the difference between these two dates in a number of days.

Example 1-9

So, from “15-01-2018” to 15-01-2019,” the exact difference is one year, so we got 365 days as a result.

Like this, we can find the difference between two dates in time intervals.

Example #2 – To Find Difference in Months

Code:

Sub DateDiff_Example2()

   Dim Date1 As Date
   Dim Date2 As Date
   
   Dim Result As Long

   Date1 = "15-01-2018"
   Date2 = "15-01-2019"

   Result = DateDiff("M", Date1, Date2)

   MsgBox Result
End Sub

 Example 2

Run this code Using the F5 key, or you can run manually to show the result as given below.

Example 2-1

Example #3 – To Find Difference in Years

Code:

Sub DateDiff_Example3()

   Dim Date1 As Date
   Dim Date2 As Date

   Dim Result As Long

  Date1 = "15-01-2018"
  Date2 = "15-01-2019"

  Result = DateDiff("YYYY", Date1, Date2)

  MsgBox Result

End Sub

Example 3

Run this code using the F5 key or manually to see the result.

Example 3-1

Assignment as a Practice

I hope you have understood the function of VBA DATEDIFF. Take a look at the below homework for you. Find the difference between the below dates in “Months.”

Example 4

If you have not found the way, below is the readymade code for you.

Code:

Sub Assignment()

    Dim k As Long

    For k = 2 To 8
        Cells(k, 3).Value = DateDiff("M", Cells(k, 1), Cells(k, 2))
    Next k

End Sub

Example 4-1

You can run this code manually or press the F5 key to see the result.

Example 4-2

Recommended Articles

This has been a guide to VBA DateDiff Function. Here we learn how to use DateDiff Function to find differences in days, months, and years in Excel VBA along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

  • DATE Function in Excel
  • Date Excel Format
  • Date Function in VBA
  • DateAdd in VBA
  • VBA COUNTA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download VBA DateDiff Function Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More