DateDiff
Syntax
DateDiff(interval, date1, date2 [,firstdayofweek[,
firstweekofyear]])
Argument  Description 

interval 
Mandatory. String expression that is the interval you want to
use to calculate the differences between date1 and
date2 . The values are:

date1, date2 
Mandatory. Date expressions. Two dates you want to use in the calculation. 
firstdayofweek 
Optional. Constant that specifies the first day of the week. If
not specified, then Sunday is assumed. The values are:

firstweekofyear 
Optional. Constant that specifies the first week of the year.
If not specified, then the first week is assumed to be the week in which January 1
happens.

You can use the DateDiff
function to
determine how many specified time intervals exist between two dates. For example, to
calculate the number of days between two dates, or the number of weeks between today and the
end of the year.
To calculate the number of days between date1
and date2
, you can use either Day
of year (y)
or Day (d)
. For example, DateDiff(d,
date1,date2)
determines the number of days between date1
and date2
. You must ensure that
hardcoded dates are enclosed in quotation marks ("") as the function requires a text
string.
When interval is Weekday (w)
, DateDiff
returns the number of weeks between the two dates. If date1
falls on a Monday, then DateDiff
counts the number of Mondays until date2
. It counts date2
but not date1
.
If interval is Week (ww)
, then the DateDiff
function returns the number of calendar weeks between
the two dates. It counts the number of Sundays between date1
and date2
. DateDiff
counts date2
if it falls on a Sunday;
but not date1
, even if it does fall on a Sunday.
If date1
refers to a later point in time
than date2
, then the DateDiff
function returns a negative number.
The firstdayofweek
argument affects
calculations that use the w
and ww
interval symbols.
If date1
or date2
is a date literal, then the specified year becomes a permanent part of
that date. If date1
or date2
is enclosed in quotation marks (" ") and you omit the year, then the
current year is inserted in the code. It is inserted each time the date1
or date2
expression is evaluated. This
makes it possible to write code that can be used in different years.
When comparing December 31 to January 1 of the next year, DateDiff
for Year(yyyy)
returns "1" even though only one day has elapsed.
Examples
This example returns the number of days between a given date and today:
Function DiffADate(theDate)
DiffADate = "Days from today: " & DateDiff("d", Now, theDate)
End Function
This example returns "2":
DateDiff("yyyy","01/01/2016","01/01/2018")
This example returns "4":
DateDiff("q","01/01/2016","01/01/2017")
This example returns "12":
DateDiff("m","01/01/2016","01/01/2017")
This example returns "365":
DateDiff("y","01/01/2016","01/01/2017")
This example returns "365":
DateDiff("dd","01/01/2016","01/01/2017")
This example returns "52":
DateDiff("w","01/01/2016","01/01/2017")
This example returns "52":
DateDiff("ww","01/01/2016","01/01/2017")
This example returns "8760":
DateDiff("h","01/01/2016","01/01/2017")
This example returns "525600":
DateDiff("n","01/01/2016","01/01/2017")
This example returns "31536000":
DateDiff("s","01/01/2016","01/01/2017")