How to Use the DAYS360 Function in Excel

The DAYS360 function in Excel is a formula that calculates the number of days between two dates based on a 360-day year. The formula syntax includes the start date and end date, with an optional parameter to indicate whether to use the U.S. or European method in the calculation. It follows the format DAYS360 (start_date, end_date, [method]).

The DAYS360 function is useful for accounting calculations that use a 360-day year, where every month is assumed to have 30 days. This simplifies the calculation of interest rates and other financial metrics. In this article, we will explain the basic theory behind the DAYS360 function, how to use it in Excel, and how to create a scenario with real data to illustrate its application.

The DAYS360 function returns the number of days between two dates, based on a year where all months have 30 days. Both dates must be valid Excel dates or text values that can be parsed as dates. The DAYS360 function only works with whole numbers, time values are ignored.

The DAYS360 function takes an optional argument called method that can be set to either TRUE or FALSE. When method is FALSE (default) DAYS360 uses a U.S. method to compute days. When the start date is the last day of the month, it is treated like the 30th day of that month. When the end date is the last day of the month, and the start date is less than 30, the end date is treated as the 1st of the next month, otherwise the end date is treated like the 30th of the same month. If method is set to TRUE, DAYS360 uses a European method to calculate days. In this scheme, start and end dates equal to the 31st of a month are set to the 30th of the same month.

Procedures

To use the DAYS360 function in Excel, follow these steps:

1. Enter the start date and end date in two separate cells. Make sure they are formatted as dates or text values that can be recognized as dates by Excel.
2. In another cell, type =DAYS360( and select the cell that contains the start date, followed by a comma.
3. Select the cell that contains the end date, followed by a comma.
4. Optionally, enter TRUE or FALSE to indicate the method you want to use, or leave it blank to use the default U.S. method.
5. Close the parenthesis and press Enter. The cell will display the number of days between the two dates, based on a 360-day year.

Example

Let’s create a scenario to demonstrate how the DAYS360 function works in Excel. Suppose you want to calculate the interest rate for a loan that has a start date of January 1, 2024 and an end date of December 31, 2024. The loan amount is $10,000 and the annual interest rate is 5%. You want to use the U.S. method to calculate the number of days between the two dates.

To do this, follow these steps:

1. In cell A1, enter Start Date and format it as bold and centered.
2. In cell B1, enter End Date and format it as bold and centered.
3. In cell C1, enter Days and format it as bold and centered.
4. In cell A2, enter 1/1/2024 and format it as a date.
5. In cell B2, enter 12/31/2024 and format it as a date.
6. In cell C2, enter =DAYS360(A2,B2) and press Enter. The cell will display 360, which is the number of days between the two dates, based on a 360-day year.
7. In cell D1, enter Loan Amount and format it as bold and centered.
8. In cell E1, enter Interest Rate and format it as bold and centered.
9. In cell F1, enter Interest Amount and format it as bold and centered.
10. In cell D2, enter 10000 and format it as currency with two decimal places.
11. In cell E2, enter 0.05 and format it as percentage with two decimal places.
12. In cell F2, enter =D2*E2*C2/360 and press Enter. The cell will display $138.89, which is the interest amount for the loan, based on a 360-day year.

The final result should look like this:

Other Approaches

The DAYS360 function is not the only way to calculate the number of days between two dates in Excel. You can also use the following functions:

• The DAYS function returns the number of days between two dates based on a 365-day year, where every month has its actual number of days. For example, the formula =DAYS(B2,A2) returns 365, which is the number of days between January 1, 2024 and December 31, 2024, based on a 365-day year.
• The YEARFRAC function returns a decimal value that represents fractional years between two dates, based on a specified day count convention. For example, the formula =YEARFRAC(A2,B2,0) returns 1, which is the fractional year between January 1, 2024 and December 31, 2024, based on a 360-day year with the U.S. method. You can change the third argument to use different day count conventions, such as 1 for actual/actual, 2 for actual/360, 3 for actual/365, or 4 for European 30/360.
• The NETWORKDAYS function returns the number of working days between two dates, excluding weekends and optionally holidays. For example, the formula =NETWORKDAYS(A2,B2) returns 261, which is the number of working days between January 1, 2024 and December 31, 2024, excluding Saturdays and Sundays.