Calculate date differences

 2011-07-03    Dates    2    104

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


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2012-04-16 17:38:42 (GMT)

You can use a built-in worksheet function to do this:
NETWORKDAYS(start_date;end_date;holidays).
Or you can use a custom function from this example: Calculate Workdays.

Al | 2012-04-16 15:38:53 (GMT)

I want to calculate Days between two days but to exclude the weekends (SAturday and Sunday)....

would be greatful if you could send me the solution to my email address.

thank you in advance