The DAYS function in Google Sheets is useful when you need to know the number of days between two dates.
Given a start date and an end date, we can use the DAYS function to compute how many days transpired between them easily.
Table of Contents
The rules for using the
DAYS function in Google Sheets are as follows:
- The function takes two arguments: an end date and a start date. It must be written in that order.
- The function returns an integer that corresponds to the number of days between those two dates.
DAYSfunction factors leap days in its result.
Let’s take a look at a quick example!
Let’s say I am a local business owner who would like to encourage my customers to buy more with a loyalty program.
Customers who made their first purchase within the last 30 days get a Bronze status and receive a single coupon. Customers who achieve 60 days get a Silver status and receive two coupons, and so on. Finally, customers who have made their first purchase over a year ago will receive a Platinum membership with even more free giveaways.
I currently have a database of Customers and the date of their first purchase. How do I compute which Loyalty perks they should receive?
DAYS function it becomes quite easy to compute the number of days since the customer’s first purchase. We can wrap the result of our
DAYS function with an
IF statement or a
VLOOKUP to get the appropriate loyalty tier.
This use case is just one way to use the
DAYS function in Google Sheets. Another way we can use
DAYS is for computing how far certain dates are from the current date. For example, given a list of deadlines for requirements, we can arrange each requirement by how close their due date is and even find out which of them are already past due.
Note that the
DAYS function is not able to exclude weekends or public holidays from the count. If your use-case relies on such functionality, try checking out the NETWORKDAYS function instead.
If you want a more powerful function for finding the time difference between dates, you can use the DATEDIF. That particular function can return the difference in days, months, and even years.
Now that you know what the
DAYS function can do for you, let’s start learning how to use it. We’ll be using the function ourselves in Google Sheets and later test out the function with actual date values.
The Anatomy of the DAYS Function
So the syntax (the way we write) the
DAYS function is as follows:
Let’s dissect this thing and understand what each of these terms means:
- = the equal sign is how we start any function in Google Sheets.
- DAYS() is our
DAYSfunction. It computes the number of days between two dates.
- end_date and start_date are the two dates we’ll be using to compute the difference. The ordering is important. If the end date is earlier than the start date, the output would be a negative value.
- Whenever you call the
DAYSfunction, the function simply converts both dates into date values. For example, “January 1st, 2000” is a string, but when converted, it has a date value of 36526. The conversion allows us to perform operations on dates.
A Real Example of Using DAYS Function
Let’s look into an example of the
DAYS function being used in a Google Sheet spreadsheet.
In the spreadsheet below, the first table has a list of customers as well as the date they first purchased an item from the store. The second table will be used as a reference to get the minimum days needed to get to a certain tier.
To get the values in Column C, we just need to use the following formula:
We can proceed with getting the values in Column D by implementing a VLOOKUP function around our DAYS formula:
=VLOOKUP(DAYS(TODAY(), A2), $A$9:$B$14, 2, TRUE)
You can make a copy of the spreadsheet above using the link I have attached below.
If you’re ready to try out the DAYS function in Google Sheets, let’s begin writing it ourselves!
How to Use DAYS Function in Google Sheets
- Let’s use the
DAYSfunction to sort a tracker for requirements. We have below a spreadsheet with the name of the requirement and the date it’s due. We’ll use the
DAYSfunction to determine how many days are left before the requirements due date and which of those requirements are already late.
- To start using the
DAYSfunction, select the cell we will first put our function’s output. In this example, we’ll start with cell C2
- Next, we just simply type the equal sign ‘=‘ to begin the function, followed by ‘DAYS(‘.
- As seen below, a tooltip box appears with info on the
DAYSfunction. We can click on the arrow on the top-right-hand corner of the box to minimize it if necessary.
- The next step is to type out the two dates needed. In this example, we need to compare the due date from the current date. The due date can be accessed in cell C2, and we can obtain the current date with the
Afterward, simply hit Enter on your keyboard to let the function evaluate. We now know that our first requirement is due 14 days from now.
- To compute the Late flag, we can just use the formula
=C2<0. This works because a negative value in Column C indicates that the current date is already past the due date.
- Finally, we can fill out both columns with the formulas we typed earlier by placing the cursor at the bottom-right part of the selected cell and dragging it down. Now we know that our Marketing Proposal is late by 14 days!
That’s all you need to know on how to use the
DAYS function in Google Sheets. This step-by-step guide shows how easy it is to find out the number of days between two dates.
You can now use the
DAYS functions in Google Sheets together with the various other Google Sheets formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.