VBA DateDiff Function

The 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 do not need a special formula to calculate the difference between two dates.

For example, look at the below image.

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

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

This article will show you how to use this DateDiff function in VBA.

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

Interval: This is nothing but in what way you want to calculate the date difference. The same list is below, whether in days, months, weeks, quarters, etc.

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

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

Here, the formula is Date 2 – Date 1.

: What is the first day of the week? We can agree with the following arguments.

: What is the year's first week? 

: What is the year's first week? We can enter the following arguments.

The following are examples of Excel VBA DateDiff.

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

Step 2: Define Two Variables as Date.

Code:

Sub DateDiff_Example1()

   Dim Date1 As Date
   Dim Date2 As Date

End Sub

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

Step 4: Now, define one more variable, “As Long,” to store results.

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

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

Step 6: The first argument is what kind of difference we need between these dates. 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

Step 7: What is the first date to find the difference? Our first date is "15-01-2018," which 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

Step 8: What is the second date to find the difference? The second date is "15-01-2019," which holds the value through the 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

Step 9: Ignore the last two parameters. Now, assign the variable "Result" value 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

Now, run the code using the F5 key or manually. We will get the difference between these two dates in numbers.

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

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

Run this code using the F5 key. You can run it manually to show the result as given below.

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

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

Assignment as a Practice

We 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."

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

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

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