# 4 Examples to Learn Excel / VBA DateDiff Function

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.

### 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 1st Jan 2021 to 31st 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:

 Interval Description yyyy Year q Quarter m Month y Day of year d Day w Weekday ww Week h Hour n Minute s Second

### 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:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday

Similarly, firstweekofyear possible values are:

Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with the week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Start with the first full week of the year.