Calculate date differences

 2011-07-03    Dates    2    309

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:
dCount of days between the two dates
mdCount 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)
ydCount of days between the two dates if they were in the same year
mCount of months between the two dates
ymCount of months between the two dates if they were in the same year
yCount of years between the two dates
If the 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