7

How To Calculate Days Between Dates in Google Sheets

Posted by Robert Hayes on August 31, 2019

Spreadsheet users everywhere rejoiced when Google Sheets was released to the world back in 2006. A powerful, cloud-based spreadsheet software package that’s completely free to use, Sheets has an easy learning curve and is a great tool for creating spreadsheets for work, school, or personal use. Of course, free software that’s easy to use is not going to have the feature set of the market-leading packages like Microsoft Excel. Sheets is somewhat lacking in some features and functionality, but it is still a highly-capable software package.

One of the most common uses for spreadsheet programs is to create calendars and to handle information about dates, such as time sheets or vacation schedules. Many users who create a spreadsheet dealing with dates find themselves needing to calculate how many days there are between two dates; that is, they need to find how many days there are between (as an example) July 1, 2018 and January 31, 2019. You could just look on a calendar and count the days up by hand, and that would work fine if the dates were very close together, but for large numbers of dates or dates that are far apart, a little help from the computer would sure be nice. Fortunately, Google Sheets has a number of ways to find the number of days between two dates.

The MINUS Function

Unlike Excel, Google Sheets has a subtraction function which is very handy for calculating simple date differences. MINUS is Sheets’ subtraction function and because of the way dates are stored internally (as integers describing the number of days since a certain date in the past), it works just fine for deducting one date from another, as long as the dates are both in the same format. The syntax for MINUS is: =MINUS(value 1, value 2).

To use MINUS, open a blank Google Sheets spreadsheet in your browser. Enter (as an example) ‘4/4/2017’ and ‘5/15/2017’ in cells B3 and C3. Now select cell D3, which is where we’ll put the MINUS function. Click inside the fx bar, and then input ‘=MINUS(C3, B3)’ and press Enter. Cell D3 will now return the value 40, as shown directly below.

google dates

So there are 40 days between 4/5/2017 and 5/15/2017. You can also find the difference between the dates just by entering the cell references and not bothering with the MINUS function. For example, click cell E3 and input ‘=C3-B3’ in the function bar, as shown in the snapshot directly below. That will also return 40, although since you are directly subtracting dates without MINUS, the value in cell E will probably display in date format and look very strange. You can convert the cell format to show an integer value by selecting Format > Number and Number.

google dates2

You might also input the cell references with the earlier date first. If you entered ‘=B3-C3’ in the function bar, the cell would contain the value -40. This highlights that 4/4/2017 is 40 days behind 5/15/2017.

The DATEDIF Function

DATEDIF is a function that you can find the total days, months or years between two dates with. You can find the total days between two dates entered on the spreadsheet or include the dates within DATEDIF instead. The syntax for DATEDIF is: DATEDIF(start_date, end_date, unit). The unit for the function can be D (days), M (months) or Y (years).

To find the difference between 4/4/2017 and 5/15/2017 with DATEDIF, you should select a cell to add the function to (F3, in our case) and input ‘=DATEDIF’ in the fx bar. Then expand the function with brackets that include the start date and end date cell references B3 and C3. The unit days, otherwise “D,” should also be at the end of the function. So the full function is =DATEDIF(B3, C3, “D”), which returns the value 40, as below.

google dates3

DATEDIF will also work if you put the date information directly into the formula. Click a spreadsheet cell to add DATEDIF to, and then input ‘=DATEDIF(“4/5/2017”, “5/15/2017″,”D”)’ in the fx bar. That will return 40 in the selected cell as shown below.

google dates4

The DAY360 Function

Google Sheets includes DAY360, which calculates the difference between dates for a 360 day year. The 360 day calendar is used primarily for financial spreadsheets, in which interest rate calculations might be required. The syntax for DAYS360 is: =DAYS360(start_date, end_date, [method]). The [method] is an optional indicator you can include for the day count method.

To use this function to your Google Sheets spreadsheet for the dates 1/1/2016 and 1/1/2017, enter ‘1/1/2016’ in cell B4 as the start date, and then input ‘1/1/2017’ in C4 as the end date for the function. Now select cell D4, input the function ‘=DAYS360(B4, C4)’ in the fx bar and press Enter. Then cell D4 will include a total of 360 days between the selected dates. Note that the only real use for this particular function is if you are working with interest rates.

google dates5

The NETWORKDAYS Function

NETWORKDAYS also calculates the number of days between dates, but it’s not entirely the same as the others. This function only counts the weekdays, so it leaves weekends out of the equation. (Read it as “Net Workdays” rather than “Network Days”.) As such, you can find the total number of weekdays between a couple of dates with NETWORKDAYS, and you can also specify extra holidays so that it excludes other dates. The syntax for NETWORKDAYS is: NETWORKDAYS(start_date, end_date, [holidays]).

You can add this function to your spreadsheet with the example dates 4/4/2017 and 5/15/2017 entered in cells B3 and C3. Select a cell to include the day total in, and click in the fx bar to insert the function. Input ‘=NETWORKDAYS(B3, C3)’ and press the Enter key to add the function to whichever spreadsheet cell you’ve chosen for it. The NETWORKDAYS cell will include the total 29 for the number of days between the dates.

To add a holiday date to the function, first enter ‘4/17/2017’ in cell A3. Select the NETWORKDAYS cell, click the fx bar, and modify the function by adding the cell reference A3 to it. So the function would then be =NETWORKDAYS(B3, C3, A3), which will return 28 with the extra bank holiday also deducted from the total days.

google dates6

Other Important Date-Related Functions

There are a number of date-related functions in Sheets that you should be familiar with if you are going to be doing a lot of work with dates.

    • The DATE function converts a provided year, month, and day into a date. The format is DATE(year,month,day). For example, DATE(2019,12,25) returns “12/25/2019”.
    •  The DATEVALUE function converts a properly-formatted date string into a date integer. The format is DATEVALUE(date string); the date string can be any appropriate string, such as “12/25/2019” or “1/23/2012 8:5:30”.
    • The DAY function returns the day of the month that a specific date falls on, in numeric format. The format is DAY(date). For example, DAY(“12/25/2019”) returns 25.
    • The DAYS function returns the number of days between two dates. The format is DAYS (end date, start date). For example, DAYS(“12/25/20189”, “8/31/2019”) returns 116.
    • The EDATE function returns a date a specific number of months before or after the given date. The format is EDATE(start date, number of months). For example, EDATE(“8/31/2019”, -1) returns “7/31/2019”.
    • The MONTH function returns the month of the year that a specific date falls on, in numeric format. The format is MONTH(date). For example, MONTH(“8/30/2019”) returns 8.
    • The TODAY function returns the current date as a date value. The format is TODAY(). For example, at the time of this writing, TODAY() would return “8/31/2019”.
    • The WEEKDAY function returns a numeric value showing the day of the week of the date provided. The format is WEEKDAY(date, type) and type can be 1, 2, or 3. If type is 1, days are counted from Sunday and Sunday has a value of 1. If type is 2, days are counted from Monday and the value of Monday is 1. If type is 3, days are counted from Monday and the value of Monday is 0. For example, 4/30/2019 is a Tuesday, and WEEKDAY(“4/30/2019”,1) would return 3, while WEEKDAY(“4/30/2019”,2) would return 2 and WEEKDAY(“4/30/2019”,3) would return 1.
    • The YEAR function returns a numeric value showing the year of the date provided. The format is YEAR(date). For example, YEAR(“12/25/2019”) would return 2019.

That’s how you can find the number of days between dates in Google Sheets. The MINUS, DATEDIF, NETWORKDAYS and DAYS360 functions will certainly come in handy if your spreadsheets include lots of dates.

Have any other unique or interesting ways to work with dates in Google Sheets? Share them with us below!

7 thoughts on “How To Calculate Days Between Dates in Google Sheets”

rancher Mike says:
Thank you for the nice tutorial. Can you tell me how I can apply the date calculation to all rows in columns? For example each row column c has a date, and each row column d has a date. I want it to calculate the days from the dates in each row in column c through the dates in each row in column d and show totals days in column e.
Thank you
P.S. I am using this to calculate the number of days livestock have been grazing in paddocks.
Reply
Amr ALHOSSARY says:
This is not always useful. The week in Egypt and most (if not all) Arab countries, starts on Saturday.
Reply
Daniel says:
How would this work if i’m trying to conditional format a cell to a) highlight green for dates between today and 3 months ago b) highlight yellow for dates between 3 months and 6 months ago and on?
Reply
B says:
For anyone struggling to get this to work you must use the Americanised date format of month-date-year. Took me some time to work out what the problem was since for those of us outside the American-centric thought bubble, it didn’t even occur that this might be a strict requirement.
Reply
Rico says:
If you first go to File > Spreadsheet settings and change the Locale (and Time zone if appropriate) this will make life much easier ie you won’t have to convert to US date format. Although you’ll likely have to change the existing US date formats you have dates back to the format you want to use.
Reply
Jonathan says:
Very helpful – thank you!
Reply
naresh says:
Could Anyone tell Which Date function will work for finding dates array inbetween start Date and End Date
Reply
Adam Steinfurth says:
The DAYS functions can do this, right?
Reply

Leave a Reply to rancher Mike Cancel reply

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


Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.