DIFFERENCE IN MONTHS
There will be business scenarios when you will be asked to come up with duration between two dates in terms of months.
— Note the Excel file for this article can be downloaded from DateDiff —
Let's Say A1 has From Date of 10/5/2013 and B1 has To Date. You may use following formulas –
=DATEDIF(A1,B1,"m")
Now, this gives us the answer in Completed Months. It will not give fractional answer like 1.6 months.
If you want fractional answer, then you can subtract From Date and To date and divide by 30 to arrive at fractional months. (30 days basis will give 360 days in a year whereas we have 365 days in a year. Hence, there will be some margin of error which is generally accepted)
If you want to use 365 days in a year, you can divide the difference of dates (which will be in days) by 365/12 rather than 360/12 = 30. (Please note, I am still not talking about Leap Year which will have 366 days. For all computational purposes, we will have to accept that either we need to use 360/12 or 365/12 to derive fractional year.
Results on the basis of above formulas are below.
DIFFERENCE IN YEARS
You can use following formula for Completed Years not Fractional Years.
=DATEDIF($A$2,B2,"y")
If you want fractional years, you can simply subtract the dates and divide by 360 or to become more accurate, by 365.
Formulas to be used
=(B2-A2)/360
=(B2-A2)/365
Now Excel provides a Function which gives us Fractional Year. That function is YEARFRAC and takes care of 366 days in the Leap Year if we choose basis of 1. It has syntax of
YEARFRAC(start_date, end_date, [basis])
Where Basis is
0 – US (NASD) 30/360 Where End Date – Start Date = US(NASD)30 which is divided by 360.
Now, what is US(NASD)30 (Source: http://en.wikipedia.org/wiki/360-day_calendar) –
A duration is calculated as an integral number of days between two dates A and B (where by convention A is earlier than B). There are two methods commonly available which differ in the way that they handle the cases where the months are not 30 days long:
- The European Method (30E/360)
- If either date A or B falls on the 31st of the month, that date will be changed to the 30th;
- Where date B falls on the last day of February, the actual date B will be used.
- The US/NASD Method (30US/360)
- If both date A and B fall on the last day of February, then date B will be changed to the 30th.
- If date A falls on the 31st of a month or last day of February, then date A will be changed to the 30th.
- If date A falls on the 30th of a month after applying (2) above and date B falls on the 31st of a month, then date B will be changed to the 30th.
1- Actual/Actual – Denominator is either 365 or 366 depending upon whether leap year or not leap year. In below picture, row 8 and 9 have the dates of Leap (2016) and Non Leap (2017) years. K8 and K9 have duration in dates / 366 for Leap and 365 for non Leap Year. G8 and G9 have YEARFRAC formula which yields the same result (K8,K9, G8 and G9 are coded in same colour). It demonstrates what is ACTUAL in denominator.
2- Actual/360 – This is equivalent to =(B2-A2)/360
3- Actual/365 – This is equivalent to =(B2-A2)/365
4- European 30/360 – Already explained above
