The DAYS360 function is categorized as a DATE/TIME function in Excel and allows users to calculate the number of days between any two given dates based on a 360-day year (i.e., 30 days for each month).
Note that the DAYS360 function doesn't work on the actual 365/366 days per year. For instance, if you entered January 1, 2021, and December 31, 2021, as start and end dates respectively, the function will return 360.
There are several use-cases for this function, especially for financial analysts. For instance, they can use the function while calculating the age of receivables and payables accounts.
Syntax
The syntax of the DAYS360 function is as follows:
=DAYS360(start_date, end_date, [method])Arguments:
'start_date' – This is a required argument where you insert a valid Excel date from which you want the calculation of days to begin.
'end_date' – This is a required argument where you insert a valid Excel date at which you want the calculation of days to end.
'method' – This is an optional argument where you insert a Boolean value, i.e., TRUE or FALSE. The effect of using the Boolean value is discussed in the following table. By default, the argument is set to FALSE.
| Method Value | Method Type | Description |
|---|---|---|
FALSE | U.S. (NASD) Method | Based on this method – If the method argument is omitted or set to FALSE, the formula uses the U.S. (NASD) method. The start_date is set to the 30th of the same month.
|
| TRUE | European Method | Based on this method – if the starting and ending dates are set as the 31st of a month, they are treated as the 30th of the same month. |
Important Characteristics of the DAYS360 function
- The DAYS360 function has three arguments, two of which (start_date and end_date) only accept numeric values or valid Excel dates, and one that only accepts a Boolean value.
- The DAYS360 function returns a #VALUE! error, if either the start_date or end_date argument is passed as a text value that can not be interpreted as a date.
- If the dates entered in either the start_date or end_date argument are out of the Excel's recognized range, the function returns a #NUM! error.
Examples
Let's try to see some examples of the DAYS360 function.
Example 1 – Plain Vanilla Formula for the DAYS360 Function
The DAYS360 function is pretty simple when used without the method argument. Let's do that to see just what it's trying to do when we enter two dates into its arguments. Following is the formula we'll use:
=DAYS360("12-25-2000","01-31-2001")
The formula returns 36. The DAYS360 function takes a no-fuss approach and assumes all months to have 30 days, so it doesn't matter that both January and December have 31 days. What the function essentially did here is that it added 5 days for December, 30 days for January, and 1 day for February.
So, why is February barging in on our DAYS360 party uninvited?
Well, refer to the table for the method argument. When it's omitted and the end_date is set as the is the last day of the month and the start_date is less than the last day of the month then the end_date is treated to be the first day of the following month (February 1, 2001, in this case).
Good so far? Let's throw the method argument into the mix.
Example 2 – DAYS360 Function with 'method' set to TRUE
We've already inadvertently used the method argument in the previous example because it's set to FALSE by default. What we're about to look at now is what the formula does when the method argument is set to TRUE.
We'll use the same data to see how the output changes, and apply the following formula:
=DAYS360("12-25-2000","01-31-2001", TRUE)
Go to the table again. You'll see that when the method is set to TRUE and if the start_date or end_date is set as 31st of the month then it gets treated as 30th of the same month.
Therefore, the only difference is that instead of setting the end_date to February 1 as it did in the previous example, the end_date in this case will be set to January 30. The final computation in that case gives us 35 (i.e., 5 days of December and 30 days of January).
Still with me? If at any point you feel lost, go back to the table and that will make everything crystal clear.
Example 3 – Use DAYS360 Function with the DATE Function
Say you have a software that prepares reports in a different format. It lists the start and end dates of a project and uses the header to categorize it year-wise. When you import this into an Excel sheet, this is what it looks like:
Now suppose you want to obtain the per-day cost for each of these projects and you need the number of days spent on each project. You've read this article on ExcelTrick and you immediately know what function to use, but you also know you need valid Excel dates for using the DAYS360 function.
No worries though. You can still use the function as long as you're somehow able to convert this data into valid Excel dates. That's where the DATE function lands on the floor wearing a red cape and reveals this formula to us:
=DAYS360(DATE($A$1,B4,A4),DATE($A$1,D4,C4))
The DAYS360 function just works like it normally would; the star of this formula is the DATE function. It creates a valid Excel date out of the date value elements (year, month, and day) that are inserted in separate cells. It then relays these date values to the start_date and end_date arguments in the DAYS360 function, and voila we have the number of days spent on each project.
Recommended Reading: Excel DAYS Function
That's all there's to learn about the DAYS360 function so feel free to call yourself a champion. Alright, while you master these formulas, we'll come back for you with another top-notch function.
About Supriya