Excel Days360 Function to calculate day difference

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

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *