ExcelFunctions.net

Search Site:

Related Function:

VBA DateAddThe VBA DateDiff Function returns a Long data value representing the number of intervals between two supplied dates/times. The type of interval (e.g. hours, days, months, etc.) is specified by the user.

The syntax of the DateDiff function is:

DateDiff( Interval, Date1, Date2, [FirstDayOfWeek], [FirstWeekOfYear] )

Where the function arguments are:

Interval | - | A string specifying the interval to be used. This can have any of the following values:
| |||||||||||||||||||||||||||

Date1 | - | A date value, representing the start date/time for the calculation. | |||||||||||||||||||||||||||

Date2 | - | A date value, representing the end date/time for the calculation. | |||||||||||||||||||||||||||

[FirstDayOfWeek] | - | An optional FirstDayOfWeek enumeration value, specifying the weekday that should be used as the first day of the week. This can have any of the following values:
If omitted, the [FirstDayOfWeek] argument uses the default value | |||||||||||||||||||||||||||

[FirstWeekOfYear] | - | An optional FirstWeekOfYear enumeration value, specifying the week that should be used as the first week of the year. This can have any of the following values:
If omitted, the [FirstWeekOfYear] argument uses the default value |

' Calculate the number of days between 11/29/2015 and 12/31/2015 Dim dt1 As Date Dim dt2 As Date Dim nDays As Long dt1 = #11/29/2015# ' nDays now has the value 32dt2 = #12/31/2015# nDays = DateDiff( "d", dt1, dt2 ) |

In the above example, the DateDiff function calculates the number of days between the dates 11/29/2015 and 12/31/2015 and returns the result *32*.

I.e. there are 32 days between the two supplied dates.

' Calculate the number of hours between 11/29/2015 9:00 and 11/30/2015 12:00 Dim dt1 As Date Dim dt2 As Date Dim nHours As Long dt1 = #11/29/2015 9:00:00 AM# ' nHours now has the value 27dt2 = #11/30/2015 12:00:00 PM# nHours = DateDiff( "h", dt1, dt2 ) |

In the above example, the DateDiff function calculates the number of hours between 09:00 on 11/29/2015 and 12:00 on 11/30/2015 and returns the result *27*.

If the Interval argument is specified to be "w" (weeks), the DateDiff function returns the number of __whole__ weeks between the two supplied dates. Partial weeks are ignored.

This is illustrated in the following example:

' Calculate the number of weeks between 11/29/2015 and 12/8/2015 Dim dt1 As Date Dim dt2 As Date Dim nWeeks As Long dt1 = #11/29/2015# ' nWeeks now has the value 1dt2 = #12/8/2015# nWeeks = DateDiff( "w", dt1, dt2 ) |

In the above example, the DateDiff function calculates the number of whole weeks between the dates 11/29/2015 and 12/8/2015 and returns the result *1*.

I.e. there is one __whole__ week between the two supplied dates.

If the Interval argument is specified to be "ww" (calendar weeks), the DateDiff function returns the number of weeks between the start of the week containing the supplied Date1 and the start of the week containing the supplied Date2.

This is illustrated in the following example:

' Calculate the number of calendar weeks between 11/29/2015 and 12/8/2015 ' First day of the week = Monday Dim dt1 As Date Dim dt2 As Date Dim nWeeks As Long dt1 = #11/29/2015# ' nWeeks now has the value 2dt2 = #12/8/2015# nWeeks = DateDiff( "ww", dt1, dt2, vbMonday ) |

After running the above section of VBA code, the variable nWeeks has the value *2*.

I.e. when Monday is specified as the first day of the week, there are two weeks between the start of the week containing the date 11/29/2015 and the start of the week containing the date 12/8/2015.

If the interval is specified to be months, quarters or years, the DateDiff function simply subtracts the months, quarters and/or years during which each of the supplied dates occur.

Therefore,the function will return 1 for the number of months between the last day of November 2015 and the first day of December 2015, but will return 0 for the number of months between the first and last day of December 2015.

This is shown in the following example:

' Calculate the number of months between 11/30/2015 and 12/1/2015 ' and the number of months between 12/1/2015 and 12/31/2015 Dim nMths1 As Long Dim nMths2 As Long nMths1 = DateDiff( "m", #11/30/2015#, #12/1/2015# ) ' nMths1 now has the value 1 and nMths2 has the value 0nMths2 = DateDiff( "m", #12/1/2015#, #12/31/2015# ) |

After running the above section of VBA code, the variable nMths1 has the value *1* and the variable nMths2 has the value *0*.