The DAYS360 function in Google Sheets is a simple function that is used to calculate the number of days in between two dates. There is a catch though, this function deals with a year as 360 days and any month as 30 days.
Table of Contents
So, if we want to know roughly how many days are in between, say Independence Day (4th of July) and New Year’s Eve (31th of December), we can use the
Notice that I said roughly because the
DAYS360 function in Google sheets won’t give you the actual number of days between these two holidays.
If you want the actual number of days, you should use the DAYS function instead.
Another way is that you can actually subtract the two dates. So, you subtract the start date (4-Jul) from the end date (31-Dec).
The number of days between Independence Day and New Year’s Eve according to the
DAYS360 function of Google Sheets is 177, While it’s actually 180 days.
So, since it doesn’t give out the actual number of days in between two dates, why is this function even created?
The reason is that some organizations calculate their interests based on 360 days years, and that’s when the use of this function comes into place.
Let’s go ahead and look deeper into this interesting function we have in Google Sheets.
The Anatomy of the DAYS360 Function in Google Sheets
As any other Google Sheets function, the
DAYS360 function has a specific structure that needs to be built in order for it to work.
So, the syntax (the way we write) of the
DAYS360 function is as follows:
=DAYS360(start date, end date, [method])
Let’s dissect this thing and understand what each of these terms means:
- = (the equal sign) is just how we tell Google Sheets that we are starting a formula.
DAYS360is the name of the function we are using.
- () These parentheses are used to host the two values we put in our function, and a comma ‘,’ must separate these values.
Note that the values hosted in any google sheets function are called arguments.
- (start date) The function’s first argument is the argument that takes the earlier date we have.
- (end date) The second argument of the function takes the later date we have.
- ([method]) This is the function’s third and final argument, and it is optional, that’s why you see it in brackets. It takes the following values: True or 1, or False or 0.
Note that if any of these two arguments weren’t correctly specified, the function would result in an error.
So, before we look into a real example for using this function, let’s see some important guidelines that we must follow for the function to work correctly.
- First things first, the two main arguments of the function must take values of the date format. Else, an error will occur.
If you want to make sure that your value is of the date format, you can set it yourself as follows:
- The earlier date must be specified in the first argument of the function (start date), and the later date must take place in the function’s second argument (end date), otherwise, you will get a negative value.
- As for the third argument of the function, which is optional, you must put in one of these 4 options: 1, 0, true or false. Otherwise you will get an error.
Now for further explanation, let’s go through the
DAYS360 function with an example, and you will understand it once you start practicing its application.
A Real Example of Using DAYS360 Function
Let’s say we want to know how old I am in days. I was born on the 18th of March in the year 1994, and today’s date is 16-Oct-21
Here’s how we will input this data in Google Sheets.
As you can see, we put the TODAY function in cell B1 to get today’s date. Then I manually input My birth date in cell B2
Now let’s look at our function and its result:
It can also be written as follows.
=DAYS360(DATE(1994, 3, 18), TODAY())
Where in the second version, we used the DATE function for the first argument to make sure that it’s of the date format.
So, what does The
DAYS360 function do?
Well, let’s dissect our time period into two parts.
- The first part is the number of days between 18th-Mar-1994 and 18th-Mar2021.
We have 27 years here, so we will multiply 27 by 360, which equals 9720 days.
- Now let’s look into the second part of our time period; the day difference between 18th-Mar-2021 and 16th-Oct-2021.
We have 7 months minus 2 days. So, we do the following calculation: (7 x 30) – 2, which equals 208 days.
- Finally, we add both parts to see the full number of days between the two dates we have. 9720 + 208 = 9928 days.
Notice that we dealt with each year as 360 days and each month as 30 days, which is exactly what the
DAYS360 function does.
Also, know that the TODAY function recalculates every day. So, if you recreate this exact data in a few days, you will get a different result.
Now that we perfectly understand the concept of the
DAYS360 function, let’s begin writing our
DAYS360 function in Google Sheets.
You may make a copy of the spreadsheet using the link I have attached below.
How to Use DAYS360 Function in Google Sheets
- Simply click on any cell to make it the active cell. For this guide, I will be selecting B3, where I want to show my formula. Then type ‘=‘.
- Now, type ‘DAYS‘ and click on the
DAYS360function, or press the Down Arrow and then press Tab to select it.
- Now, select the first argument which is the start date value which is the value in cell B2. Or you can just type ‘B2‘.
- Then, type comma ‘,‘.
- As for the second argument (end date), we put in the value in cell B1.As you can see the result is already calculated, because the third argument is only optional.
- Finally, close the parentheses, press Enter, and your result will take over.
That’s pretty much it. Congrats! You now mastered the
DAYS360 function in Google Sheets.
There are many more useful Google Sheets functions that you can enjoy learning daily.