This Excel tutorial explains how to use Excel Days360 function to calculate day difference between two dates.
Excel Days360 Function
You can easily calculate the number of days between two dates by a simple deduction using Excel formula, however when you try to calculate the number of years between two dates, most people simply divide the day difference by 365. However, because every month has different days, and due to leap year, dividing the difference by 365 is totally incorrect.
Excel Days360 Function calculates the day difference, with the assumption that each month has 30 days. This assumption is made because some accounting systems use this assumption, in fact there are different assumptions for different regulations, but Days360 Function is probably most easy to use, as you need to write VBA custom function for other assumptions. The 30 days assumption is important in order to calculate the year difference between two dates.
You can also consider using DateDif Function to calculate difference between two dates in complete year, month or day.
Syntax of Excel Days360 Function
DAYS360(start_date,end_date,[method])
| Start_date | Begin date for calculation. Refer to the use of Date Function if your data is text. |
| end_date | End date for calculation |
| Method | False (default): U.S. (NASD) method. If the starting date is the last day of a month, it becomes equal to the 30th day of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending date becomes equal to the 1st day of the next month; otherwise the ending date becomes equal to the 30th day of the same month.
TRUE: European method. Starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the same month. |
Example of Excel Days360 Function
| Formula | Result | Explanation |
| =DAYS360(DATE(2015,1,1),DATE(2016,1,1)) | 360 | Difference between two dates is 360 days. Note that the start date is not inclusive |
| =DAYS360(DATE(2015,2,1),DATE(2015,3,1)) | 30 | Difference between two dates is 30 days |
| =DAYS360(DATE(2015,2,1),DATE(2015,2,1)) | 0 | Difference between two dates is 0 day |
| =DAYS360(DATE(2015,1,1),DATE(2017,6,2))/360 | 2.4194 | Year difference between two dates |
| =DAYS360(DATE(2015,1,1),DATE(2017,6,2))/30 | 29.033 | Month difference between two dates |
To calculate the complete year and then the remaining month and day, use MOD function and INT function to help
| Formula | Result | Explanation |
| =INT(DAYS360(DATE(2015,1,1),DATE(2017,6,2))/360) | 2 | Complete year difference between two dates |
| =INT(MOD(DAYS360(DATE(2015,1,1),DATE(2017,6,2))/30,12)) | 5 | Remaining month difference |
The remaining day difference is total day difference – year difference * 360 – month difference * 30
= DAYS360(DATE(2015,1,1),DATE(2017,6,2)) - 2*360 - 5*30 = 871 - 720 - 150 = 1
Therefore, the difference between 2015 Jan 1st to 2017 Jun 2nd is 2 years 5 months and 1 day, start date not inclusive.
Outbound References
https://support.office.com/en-us/article/DAYS360-function-B9A509FD-49EF-407E-94DF-0CBDA5718C2A
