MS Excel: DATEDIFF Function (VBA)
In Microsoft Excel, the DATEDIFF function returns the difference between two date values, based on the interval specified.
The syntax for the DATEDIFF function is:
DateDiff( interval, date1, date2, [firstdayofweek], [firstweekofyear] )
interval is the interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values.
|y||Day of year|
date1 and date2 are the two dates to calculate the difference between.
firstdayofweek is optional. It is a constant that specifies the first day of the week. If this parameter is omitted, Excel assumes that Sunday is the first day of the week.
firstweekofyear is optional. It is a constant that specifies the first week of the year. If this parameter is omitted, Excel assumes that the week containing Jan 1st is the first week of the year.
- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Type of Function
- VBA function (VBA)
VBA Function Example
The DATEDIFF function can only be used in VBA code. Here are some examples of what the DATEDIFF function would return:
|DateDiff("yyyy", "22/11/2003", "22/11/2013")||would return 10|
|DateDiff("q", "22/11/2003", "22/11/2013")||would return 40|
|DateDiff("m", "22/11/2011", "1/1/2012")||would return 2|
For example, you could use the DATEDIFF function in VBA code and create the following function:
Function TestDates (pDate1 as Date, pDate2 as Date) as Long TestDates = DateDiff("d", pDate1, pDate2) End Function
Based on the spreadsheet above, the function would return the following values:
|=TestDates(A2, A1)||would return 1|
|=TestDates(A2, A3)||would return 349|
|=TestDates(A4, A3)||would return 14|