DAYS360 Formula: Complete Guide to Calculating Days in a 360-Day Year
=DAYS360(start_date, end_date, [method])The DAYS360 function is a specialized Excel formula designed to calculate the number of days between two dates using the 360-day year convention, commonly known as the 30/360 method. This formula is particularly valuable in financial calculations, bond pricing, and interest accrual scenarios where the 360-day year standard is industry practice. Unlike the standard DAYS function which counts actual calendar days, DAYS360 assumes each month has 30 days and each year has 360 days, making it essential for financial professionals working with securities, loans, and derivatives. Understanding DAYS360 is crucial for anyone involved in financial analysis, accounting, or bond market operations. The formula accommodates two different calculation methods: the US method (NASD standard) and the European method (30/360 ISDA), allowing flexibility based on your specific financial context. Whether you're calculating accrued interest, determining bond yields, or managing financial instruments, DAYS360 provides the precision needed for accurate financial reporting and compliance with industry standards.
Syntax & Parameters
The DAYS360 formula follows the syntax: =DAYS360(start_date, end_date, [method]). The start_date parameter is required and represents the beginning date from which you want to count days. The end_date parameter is also required and specifies the ending date for your calculation. These dates can be entered as date values, cell references containing dates, or text strings in recognized date formats. The optional method parameter determines which calculation convention to use. When method is FALSE or omitted, the formula applies the US method (NASD standard), which adjusts the end date if it falls on the 31st of a month. When method is TRUE, the formula uses the European method (30/360 ISDA), which treats both the 31st day and the last day of February more uniformly. This distinction is critical in financial calculations where different markets and instruments may require different conventions. Practical tip: Always verify which method your financial institution or regulatory body requires before implementing DAYS360. The difference between methods can impact interest calculations significantly. Additionally, ensure your dates are properly formatted as date values rather than text to avoid calculation errors. When working with historical data, consider that DAYS360 treats all months as having 30 days, which may differ from actual calendar calculations by several days annually.
start_dateend_datemethodPractical Examples
Bond Interest Accrual Calculation
=DAYS360(DATE(2024,1,15),DATE(2024,3,20),FALSE)This formula calculates the days between January 15 and March 20 using the US NASD method. The FALSE parameter applies the standard US convention where the 31st day is adjusted. The result represents the exact number of days for accrued interest calculation on the bond.
Loan Interest Calculation with European Method
=DAYS360(DATE(2024,2,28),DATE(2024,5,31),TRUE)This formula uses the European method (TRUE parameter) which provides consistent treatment of month-end dates. Both February 28 and May 31 are handled according to the ISDA standard, ensuring compliance with European financial regulations and consistent interest calculations.
Project Duration in Financial Reporting
=DAYS360(A2,B2)Where A2 contains the start date (April 10, 2024) and B2 contains the end date (December 15, 2024). This uses the default US method. The 360-day calculation provides standardized duration metrics for financial planning and resource allocation based on industry conventions.
Key Takeaways
- DAYS360 calculates days between two dates using the 360-day year convention (30 days per month), essential for financial instruments like bonds and derivatives
- The method parameter determines the calculation standard: FALSE (US NASD method) or TRUE (European 30/360 ISDA method), with significant implications for financial accuracy
- Always use the DATE function to construct dates in DAYS360 formulas to ensure consistent results across different Excel versions and regional settings
- DAYS360 differs fundamentally from DAYS (actual calendar days) and DATEDIF (flexible date units), making it specifically suited for financial calculations requiring standardized day counts
Pro Tips
Always use the DATE function to construct dates in DAYS360 rather than text strings to ensure consistent interpretation across different Excel versions and regional settings: =DAYS360(DATE(2024,1,15),DATE(2024,3,20)) instead of =DAYS360("1/15/2024","3/20/2024").
Impact : Prevents #VALUE! errors and ensures your formulas work correctly regardless of user location or system date settings, critical for financial calculations that must be portable across different environments.
Create a helper column documenting which method (US or European) you're using for each calculation, especially when mixing DAYS360 formulas in a complex financial model. Add a comment or adjacent column: US Method vs European Method.
Impact : Improves transparency and auditability of your financial models, making it easier for colleagues to understand your calculations and ensuring compliance with regulatory requirements that often mandate specific calculation methods.
Test DAYS360 results against your financial institution's published day count calculations to verify you're using the correct method parameter. Many banks publish example calculations that you can use as validation benchmarks.
Impact : Catches method parameter errors early before they propagate through your financial models, potentially preventing significant calculation errors in interest, yield, or pricing calculations that could impact financial reporting.
For month-end processing, be aware that DAYS360 treats the 31st as the 30th in US method. Document this behavior when month-end dates are involved: =DAYS360(DATE(2024,1,31),DATE(2024,2,29)) returns 29 days, not 30.
Impact : Ensures accurate month-end financial calculations and prevents confusion when reconciling your Excel calculations with accounting systems that may handle month-end dates differently.
Useful Combinations
Calculate Accrued Interest with DAYS360 and Interest Rate
=DAYS360(start_date,end_date)*annual_interest_rate/360This combination calculates accrued interest by multiplying the 360-day count by the annual interest rate divided by 360. This is the standard formula in bond markets for calculating accrued interest on a pro-rata basis. For example: =DAYS360(A2,B2)*C2/360 where C2 contains the annual rate, calculates interest accrued during the period.
Conditional Day Count Based on Date Range
=IF(end_date>DATE(2024,6,30),DAYS360(start_date,end_date,TRUE),DAYS360(start_date,end_date,FALSE))This combination switches between calculation methods based on whether the end date falls before or after a specific cutoff date. Useful when transitioning between regulatory standards or when different portions of a year require different calculation methods. Provides flexibility in complex financial scenarios.
Average Daily Interest with DAYS360 and SUM
=SUM(principal_amounts)/DAYS360(period_start,period_end)*DAYS360(period_start,period_end)This combination calculates average daily interest by dividing the sum of principal amounts by the number of 360-day convention days. Useful in financial reports where you need to show average daily balances or interest calculations across multiple accounts or periods using the standardized 360-day year.
Common Errors
Cause: The start_date or end_date parameter contains text that cannot be recognized as a valid date format, or the method parameter contains a value other than TRUE, FALSE, 0, or 1.
Solution: Ensure dates are properly formatted using the DATE function: =DAYS360(DATE(2024,1,15),DATE(2024,3,20)) or verify text dates match your system's date format. Check that method parameter is only TRUE/FALSE or 0/1.
Cause: The formula is misspelled as 'DAYS360' with incorrect capitalization or syntax, or the function is being used in an older Excel version that doesn't support it, or there's a language-specific function name issue.
Solution: Verify the formula spelling is exactly DAYS360. Check your Excel version supports the function (2007 and later). In non-English Excel versions, use the localized function name (e.g., DIAS360 in Spanish versions).
Cause: The start_date is later than the end_date, resulting in a negative calculation that some financial systems reject, or the dates are outside Excel's valid date range (January 1, 1900 to December 31, 9999).
Solution: Verify that start_date is earlier than end_date. If calculating days in reverse is needed, use ABS function: =ABS(DAYS360(end_date,start_date)). Ensure all dates fall within Excel's recognized date range.
Troubleshooting Checklist
- 1.Verify dates are properly formatted as date values using DATE function, not text strings, to ensure Excel interprets them correctly
- 2.Confirm the method parameter is either TRUE, FALSE, 0, 1, or omitted (defaults to FALSE for US method)
- 3.Check that start_date is earlier than end_date; if reversed, the result will be negative which may cause downstream formula errors
- 4.Validate your result against your financial institution's published calculations or examples to confirm you're using the correct method
- 5.Ensure dates fall within Excel's valid range (January 1, 1900 to December 31, 9999) and that no cells contain errors that propagate to DAYS360
- 6.Test in multiple Excel versions if your spreadsheet will be used across different versions, as date handling may vary slightly
Edge Cases
Both start_date and end_date are the same date
Behavior: DAYS360 returns 0, indicating zero days have elapsed between identical dates
Solution: This is expected behavior; verify your date inputs if you expected a different result
Useful for validation checks in financial models
Start date is the 31st of a month using US method (FALSE)
Behavior: The 31st is treated as the 30th in calculations, potentially reducing the day count compared to DAYS function
Solution: Understand this is inherent to the 360-day convention; document this behavior in your model
For example, =DAYS360(DATE(2024,1,31),DATE(2024,2,29),FALSE) returns 29, not 30
Dates span across leap years with February 29th involved
Behavior: DAYS360 treats February as having 30 days regardless of leap year status; February 29 is treated as day 30
Solution: This is standard 360-day convention behavior; use DAYS function if you need actual calendar day accounting
The 360-day convention specifically ignores leap year variations, which is why it's used in financial markets for standardization
Limitations
- •DAYS360 cannot account for business days or holidays; it calculates calendar days only using the 360-day convention. For business day calculations, use NETWORKDAYS function instead.
- •The 360-day convention assumes all months have 30 days, which can create discrepancies of several days annually compared to actual calendar calculations. This is intentional for financial standardization but may not suit general business calculations.
- •DAYS360 cannot be used in array formulas or with conditional logic as easily as some alternative functions; complex scenarios may require helper columns or more sophisticated formula structures.
- •The method parameter only offers two options (US and European); there's no flexibility for custom day count conventions. If your financial instrument requires a non-standard convention, you'll need to create custom formulas.
Alternatives
Provides more granular date difference calculations with options to return days, months, or years. Works with actual calendar days rather than 360-day convention.
When: Use DATEDIF for general date calculations when you need actual calendar days or when calculating age, project duration, or employment length. Not suitable for financial instruments requiring 360-day convention.
Simpler syntax (=DAYS(end_date, start_date)) and calculates actual calendar days. More intuitive for non-financial calculations and provides accurate accounting of leap years.
When: Use DAYS for general business calculations like project timelines, customer tenure, or any scenario requiring actual calendar days. Not appropriate for bond pricing or financial instruments using 360-day standards.
Calculates the fraction of a year between two dates using various day count conventions including Actual/360, Actual/365, and 30/360. Provides decimal year fractions useful for financial calculations.
When: Use YEARFRAC when you need to express date differences as year fractions for interest rate calculations, financial modeling, or when working with multiple day count conventions in the same spreadsheet.
Compatibility
✓ Excel
Since 2007
=DAYS360(start_date, end_date, [method]) - Fully supported in Excel 2007, 2010, 2013, 2016, 2019, and 365✓Google Sheets
=DAYS360(start_date, end_date, [method]) - Identical syntax and functionality to ExcelGoogle Sheets maintains full compatibility with Excel's DAYS360 implementation. Test formulas to ensure date handling matches your expectations across platforms.
✓LibreOffice
=DAYS360(start_date, end_date, [method]) - Supported in LibreOffice Calc with same syntax as Excel