May 26, 2021

Find the Number of Days Between Two Dates in Google Sheets

Google Sheet spreadsheet
SECTIONS
  1. Subtracting the dates
  2. The DATEDIF function
  3. The DAYS function
  4. The NETWORKDAYS and NETWORKDAYS.INTL functions
  5. Sample Sheet

Subtracting the dates

Believe it or not, this works in Google Sheets! This is because Google Sheets stores the dates in its own format that allows you to perform subtraction. As proof of concept, you can check the screenshot below and the sample sheet at the end of this article (which contains this proof):

Simple subtraction can work in determining the number of days between two dates.
Simple subtraction can work in determining the number of days between two dates.


There are more sophisticated methods that you can use, including two functions for counting only the working days.

The DATEDIF function

The DATEDIF function is a general function for calculating the time separation between two dates. It requires you to specify the start date, end date, and what should be calculated. The value it outputs can be in terms of years, months, and days. The syntax of the function is set below:

DATEDIF(start_date, end_date, unit)

There are several options for the unit:

“Y” is for counting whole years

“M” is for counting whole months

“D” is for counting whole days

When adding the unit, make sure the quotation marks are included. You can point the function to the cells containing the start date and the end date. The word “whole” means the function counts only whole years/months/days. Therefore, if there is less than one year/month/day between two dates, the function will have “0” as an output. 

To find the days between two dates, simply use the “D” option. You can check the examples below:

DATEDIF function examples. First row is for counting the number of whole years between two dates. Second row is for counting the number of whole months between two dates. Third row is for counting the number of whole days between two dates. 
DATEDIF function examples. First row is for counting the number of whole years between two dates. Second row is for counting the number of whole months between two dates. Third row is for counting the number of whole days between two dates. 


The DAYS function

The DAYS function is a shortcut function if you want to find the number of whole days between two dates. It only requires the start date and the end date. It also allows you to point to the cells containing the dates. The syntax is as follows:

DAYS(end_date,start_date)

Note that the order is reversed: the end day should come first before the start date. If you got the order incorrect, the number of days will still be correctly calculated but with a negative sign. You can check the examples below:

DAYS function examples. First row uses the correct syntax. Second row got the dates reversed, thus adding a negative value. 
DAYS function examples. First row uses the correct syntax. Second row got the dates reversed, thus adding a negative value. 


The NETWORKDAYS and NETWORKDAYS.INTL functions

There are certain cases where you have to calculate the working days instead of simply the days, such as certain kinds of business transactions and even government processes. Google Sheets has a solution for this: the NETWORKDAYS function (it’s NET-WORK-DAYS). It counts the number of business days between two days, automatically ignoring the weekends. The syntax is as follows:

NETWORKDAYS(start_date, end_date, [holidays])

It also allows you to add holidays, excluding them from the count. The holidays, however, have to be in the form of Google’s date serial numbers. You can specify them as a list inside the function, or again point the function to a range of cells containing the holidays. The holidays are an optional argument. You can check the examples:

NETWORKDAYS function. The first row counts the number of working days between two dates. The second row counts the number of working days between two dates, excluding the holidays listed in the list below.
NETWORKDAYS function. The first row counts the number of working days between two dates. The second row counts the number of working days between two dates, excluding the holidays listed in the list below.


A related function is the NETWORKDAYS.INTL function. This allows you to tweak the days of the week that serve as weekends in your country. The syntax of the function is:

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

The weekends can be specified using two methods: the string method and the number method. By default, the weekend is set to 1, specifying Saturday and Sunday as the weekend days. The string method is a simpler method since it visualizes the days of the week into a string of 0’s and 1’s. Here’s a few examples:

0000011 - weekend at Saturday and Sunday

1000000 - weekend at Monday

1010100 - day-offs at Monday, Wednesday, and Friday

Make sure the weekend format is enclosed in quotation marks. You can check the examples below:

NETWORKDAYS.INTL function applied to the same start date and end date but with different definitions of “weekend”. The first row specifies the weekend on Saturday and Sunday. The second row specifies the weekend on Monday. The third row specifies the weekends or day-offs on Monday, Wednesday, and Friday. The last row specifies no day for weekend nor day-off. This gives an extra day as a result of the start date being counted.
NETWORKDAYS.INTL function applied to the same start date and end date but with different definitions of “weekend”. The first row specifies the weekend on Saturday and Sunday. The second row specifies the weekend on Monday. The third row specifies the weekends or day-offs on Monday, Wednesday, and Friday. The last row specifies no day for weekend nor day-off. This gives an extra day as a result of the start date being counted.


Sample Sheet

You can check the sample sheet below to check the functions:

Number of Days Sample Sheet

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.