Calculate date differences
2011-07-03 Dates 2 334
Calculating the difference between two dates is rather easy, just subtract one date from the other to return the count of days between the two dates. If you add a number to a date, you will get a new date that is the number of days into the future from the initial date. You can also subtract a number form a date to get a previous date. Another option is to use the worksheet function DATEDIF that can calculate the difference between two dates in a variety of different intervals, such as the count of years, months, or days between two dates. The syntax for the DATEDIF-function is: =DATEDIF(Date1;Date2;Interval) The Norwegian syntax for the function is =DATODIFF(Date1;Date2;Interval) Where Date1 is the first date, Date2 is the second date and Interval is the interval type to return. If Date1 is later than Date2, the function will return a #NUM! error. If Date1 or Date2 is not a valid date, the function will return a #VALUE error. The Interval argument can be one of these string values:
Interval: | Function Return Value: |
d | Count of days between the two dates |
md | Count of days between the two dates if they were in the same month and year. Note! The "md" argument might cause the function to return a wrong result like a negative number, a zero or an inaccurate result. You can use this workaround to calculate the remaining days after the last completed month: =EndDateRef-DATE(YEAR(EndDateRef);MONTH(EndDateRef);1) |
yd | Count of days between the two dates if they were in the same year |
m | Count of months between the two dates |
ym | Count of months between the two dates if they were in the same year |
y | Count of years between the two dates |
The DATEDIF function has not been documented much in the Excel help files by Microsoft, probably because the function was only implemented to make Excel compatible with their initial main spreadsheet rival Lotus 1-2-3. The DATEDIF function was supposedly documented in the help file for Excel 2000. In more recent days the function was documented in the Excel for Mac online function reference. The curious thing is that the DATEDIF function is also used and documented in Windows SharePoint 2003, 2007 and 2010.
Calculating Date Differences in VBA:
In VBA you can calculate date differences use a similar function, the DateDiff-function:Sub ExamplesUsingDateDiffFunction() Dim lngResult As Long, lngStartDate As Long, lngEndDate As Long lngStartDate = DateSerial(2000, 1, 1) lngEndDate = Date ' today lngResult = DateDiff("yyyy", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays) Debug.Print lngResult & " years" lngResult = DateDiff("q", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays) Debug.Print lngResult & " quarters" lngResult = DateDiff("m", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays) Debug.Print lngResult & " months" lngResult = DateDiff("w", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays) Debug.Print lngResult & " weeks (days)" lngResult = DateDiff("ww", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays) Debug.Print lngResult & " weeks (calendar)" lngResult = DateDiff("d", lngStartDate, lngEndDate, vbMonday, vbFirstFourDays) Debug.Print lngResult & " days" ' y returns the same End Sub