How To Calculate Days Between Dates in Google Sheets
Google Sheets is powerful, cloud-based spreadsheet software that’s completely free to use. With its easy learning curve and free price point, it’s a great tool for creating spreadsheets for work, school, or personal use. Compared to Microsoft Excel, however, Sheets is somewhat lacking in some features and functionality. However, it also has a lot of features and functionality that are virtually clones of what Excel offers, so don’t lose your cool just yet.
One common use 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, if for no other reason than to stop you from losing count when you get bored. 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, it works just fine for deducting one date from another. 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.
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.
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.
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.
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.
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. 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.
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. Check out this Tech Junkie post for further details on how to calculate total days between dates in Excel.
Have any other unique or interesting ways to work with dates in Google Sheets? Share them with us below!