| DateDiff Function |
Named Arguments
Yes
Syntax
DateDiff(interval, date1, date2[, firstdayofweek[,
firstweekofyear]])interval
Use: Required
Data Type: String
The units of time used to express the result of the difference between date1 and date2 (see the table Section 7.53.3).
date1
Use: Required
Data Type: Variant (Date)
The first date you want to use in the differential calculation.
date2
Use: Required
Data Type: Variant (Date)
The second date you want to use in the differential calculation.
firstdayofweek
Use: Optional
Data Type: Numeric constant
A numeric constant that defines the first day of the week. If not specified, Sunday is assumed (see the table Section 7.53.4).
firstweekofyear
Use: Optional
Data Type: Numeric constant
A numeric constant that defines the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs (see the table Section 7.53.5).
Interval Settings
| Setting | Description |
|---|---|
| yyyy | Year |
| q | Quarter |
| m | Month |
| y | Day of year |
| d | Day |
| w | Weekday |
| ww | Week |
| h | Hour |
| n | Minute |
| s | Second |
First Day of Week Constants
| 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 |
First Week of Year Constants
| 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 first full week of the year. |
Return Value
Variant (Long).
Description
Returns a variant of subtype long specifying the number of time intervals between two specified dates.
The DateDiff function calculates the number of time intervals between two dates. For example, you can use the function to determine how many days there are between 1 January 1980 and 31 May 1998.
Rules at a Glance
The calculation performed by DateDiff is always date2— date1. Therefore, if date2 chronologically precedes date1, the value returned by the functions is negative.
If interval is Weekday "w", DateDiff returns the number of weeks between date1 and date2. DateDiff totals the occurrences of the day on which date1 falls, up to and including date2, but not including date1. Note that an interval of "w" doesn't return the number of weekdays between two dates, as you might expect.
If interval is Week "ww", DateDiff returns the number of calendar weeks between date1 and date2. To achieve this, DateDiff counts the number of Sundays (or whichever other day is defined to be the first day of the week by the firstdayofweek argument) between date1 and date2. If date2 falls on a Sunday, it's counted, but date1 isn't counted even if it falls on a Sunday.
The firstdayofweek argument affects only calculations that use the "ww" (week) interval values.
Example
Dim dtNow As Date Dim dtThen As Date Dim sInterval As String Dim lNoOfIntervals As Long dtNow = Date dtThen = "01/01/1990" sInterval = "m" lNoOfIntervals = DateDiff(sInterval, dtThen, dtNow) MsgBox lNoOfIntervals
Programming Tips and Gotchas
When working with dates, always check that a date is valid using the IsDate function prior to passing it as a function parameter.
When comparing the number of years between December 31 of one year to January 1 of the following year, DateDiff returns 1 although in reality, the difference is only one day.
DateDiff considers the four quarters of the year to be January 1–March 31, April 1–June 30, July 1–September 30, and October 1–December 31. Consequently, when determining the number of quarters between March 31 and April 1 of the same year, for example, DateDiff returns 1, even though the latter date is only one day after the former.
If interval is "m", DateDiff simply counts the difference in the months on which the respective dates fall. For example, when determining the number of months between January 31 and February 1 of the same year, DateDiff returns 1, even though the latter date is only one day after the former.
To calculate the number of days between date1 and date2, you can use either Day of year "y" or Day "d".
In calculating the number of hours, minutes, or seconds between two dates, if an explicit time isn't specified, DateDiff provides a default value of midnight (00:00:00).
If you specify date1 or date2 as strings within quotation marks (" ") and omit the year, the year is assumed to be the current year, as taken from the computer's date. This allows the same code to be used in different years.