How to Use DAYS Function in Google Sheets

Using the DAYS function to calculate how far a due date is from today

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. 

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.
  • The DAYS function 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?

With the 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:

=DAYS(end_date,start_date)

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 DAYS function. 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 DAYS function, 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.

Example of using the DAYS function to compute how long ago a date is from current day

 

To get the values in Column C, we just need to use the following formula:

 =DAYS(TODAY(),A2)

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

  1. Let’s use the DAYS function 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 DAYS function to determine how many days are left before the requirements due date and which of those requirements are already late.
    Sample data to use the DAYS function with
  2. To start using the DAYS function, select the cell we will first put our function’s output. In this example, we’ll start with cell C2
  3. Next, we just simply type the equal sign ‘=‘ to begin the function, followed by ‘DAYS(‘. 
  4. As seen below, a tooltip box appears with info on the DAYS function. We can click on the arrow on the top-right-hand corner of the box to minimize it if necessary.
    Typing DAYS function in the Formula Bar
  5. 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 TODAY() function.
    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.
    DAYS Function in Google Sheets
  6. 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.
    DAYS Function in Google Sheets
  7. 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!
    DAYS Function in Google Sheets

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.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like