4 Examples to Learn Excel / VBA DateDiff Function

What is DateDiff function in VBA?

The DateDiff function in VBA is used to calculate the difference between two dates – in terms of days, years, months, quarters, hours, minutes, etc – depending on your requirements.

The DateDiff takes three mandatory arguments and two optional:

DateDiff(interval, date1, date2, [ firstdayofweek, [ firstweekofyear ]] )

We show you the usage of these arguments in detail in the examples below.

An example of using DateDiff with interval, date1, and date2 arguments

• Date1 and Date2 are self-explanatory i.e. two dates that you want to get difference.
• Interval is also straightforward – the result should be in terms of days or months etc.

In a simple example below, we will give 1^st Jan 2021 to 31^st Jan 2021 as two dates.

We will get the result in the number of days by using “d” as the interval:

Sub DateDiff_ex()


Dim dt1

Dim dt2

dt1 = #1/1/2021#

dt2 = #1/31/2021#


MsgBox ("Difference in Two Dates in Days = " & DateDiff("d", dt1, dt2))


End Sub

Result:

On that basis, you can see:

• Date1 should be the lower date
• Date2 should be date ahead

The interval values can be one of these:

The example of getting years between two dates

Sub DateDiff_ex()


Dim dt1

Dim dt2


dt1 = #1/1/2000#

dt2 = #12/31/2021#


'Get difference in years by yyyy

MsgBox ("Difference in Years = " & DateDiff("yyyy", dt1, dt2))


End Sub

Result:

Number of weeks between two dates

Sub DateDiff_ex()


Dim dt1

Dim dt2

dt1 = #1/1/2016#

dt2 = #12/31/2016#

'Number of Weeks

MsgBox ("Number of Weeks between two dates = " & DateDiff("ww", dt1, dt2))


End Sub

Output:

Using Excel sheet for all possible interval options with the current date

In this example, we will calculate all possible intervals between the date today and a given date.

All returned values are written in the Excel sheet as shown in the result.

Code:

Sub DateDiff_ex()


Dim dt1

Dim dt2

dt1 = #1/1/2022#

dt2 = Now()


Range("B2").Value = DateDiff("yyyy", dt1, dt2)

Range("B3").Value = DateDiff("q", dt1, dt2)

Range("B4").Value = DateDiff("m", dt1, dt2)

Range("B5").Value = DateDiff("y", dt1, dt2)

Range("B6").Value = DateDiff("d", dt1, dt2)

Range("B7").Value = DateDiff("w", dt1, dt2)

Range("B8").Value = DateDiff("w", dt1, dt2)

Range("B9").Value = DateDiff("h", dt1, dt2)

Range("B10").Value = DateDiff("n", dt1, dt2)

Range("B11").Value = DateDiff("s", dt1, dt2)

End Sub

Result:

As you execute this code, you will get the difference as per your system’s date at that time.

Using firstdayofweek argument

By default, Excel takes Sunday as the first day of the Week.

If you require using some other day than Sunday as the first day of the Week then specify this argument with the following possible values:

Similarly, firstweekofyear possible values are: