### 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 |

*Interval*argument is not one of the items listed in above, the function will return a #NUM error. To calculate the number of years, months, and days between two dates, you can use a formula like this, where cell A1 contains the start date and cell B1 contains the end date:

*=DATEDIF(A1;B1;"y")&" years "&DATEDIF(A1;B1;"ym")&" months "&DATEDIF(A1;B1;"md") &" days"*This formula will return a string result like:

*10 years 6 months 21 days*

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