If you want to count the number of days between two dates, you can use the DAYS, DATEDIF, and NETWORKDAYS functions in Google Sheets to do so. DAYS and DATEDIF count every day, while NETWORKDAYS excludes Saturday and Sunday.
Counting all the days between two dates
To count the days between two dates, regardless of whether the day is a weekday or a holiday, you can use the DAYS or DATEDIF functions.
Using the DAYS function
The DAYS function is the easiest to use, as long as you don’t mind excluding holidays or weekends. However, DAYS will take note of additional days celebrated in a leap year.
To use DAYS to count down between two days, open your Google Sheets spreadsheet and click on an empty cell. Scribe =DAYS("01/01/2019","01/01/2018")replacing the dates shown with your own.
Use your dates in reverse order, so put the end date first and the start date second. Using the start date first will result in DAYS returning a negative value.
As the example above shows, the DAYS function counts the total number of days between two specified dates. The date format used in the example above is the UK format, DD/MM/YYYY. If you are in the US, be sure to use MM/DD/YYYY.
You will need to use the default date format for your locale. If you want to use a different format, click File > Spreadsheet Settings and change the value of “Local Options” to another location.
You can also use the DAYS function with cell references. If you have specified two dates in separate cells, you can type =DAYS(A1, A11)replacing cell references A1 and A11 with your own.
In the example above, a difference of 29 days is recorded with the dates stored in cells E6 and F10.
Using the DATEDIF function
An alternative to DAYS is the DATEDIF function, which allows you to calculate the number of days, months, or years between two given dates.
Like DAYS, DATEDIF is leap day aware and will calculate every day, rather than limiting it to business days. Unlike DAYS, DATEDIF doesn’t work in reverse order, so use the start date first and the end date second.
If you want to specify the dates in your DATEDIF formula, click in an empty cell and type =DATEDIF("01/01/2018","01/01/2019","D")replacing the dates with your own.
If you want to use dates from cell references in your DATEDIF formula, type =DATEDIF(A7,G7,"D")replacing cell references A7 and G7 with your own.
Counting business days between two dates
The DAYS and DATEDIF functions allow you to find the days between two dates, but they count every day. If you only want to count business days and want to discount additional holidays, you can use the NETWORKDAYS function.
NETWORKDAYS treats Saturdays and Sundays as weekend days, discounting them during its calculation. Like DATEDIF, NETWORKDAYS uses the start date first, followed by the end date.
To use NETWORKDAYS, click in an empty cell and type =NETWORKDAYS(DATE(2018,01,01),DATE(2019,01,01)). Using a nested DATE function allows you to convert year, month, and date figures to a serial date number, in that order.
Replace the numbers shown with your own year, month, and date numbers.
You can also use cell references within your NETWORKDAYS formula, instead of a nested DATE function.
Scribe =NETWORKDAYS(A6,B6) in an empty cell, replacing cell references A6 and B6 with your own.
In the example above, the NETWORKDAYS function is used to calculate the business days between multiple dates.
If you want to exclude certain days from your calculations, such as days of certain holidays, you can add them to the end of your NETWORKDAYS formula.
To do that, click on an empty cell and type =NETWORKDAYS(A6,B6,{B6:D6}. In this example, A6 is the start date, B6 is the end date, and the range B6:D6 is a cell range that contains the vacation days to exclude.
You can replace cell references with your own dates, using a nested DATE function if you prefer. To do this, type =NETWORKDAYS(E11,F13,{DATE(2019,11,18),DATE(2019,11,19)})replacing the cell references and DATE criteria with your own figures.
In the example above, the same date range is used for three NETWORKDAYS formulas. With 11 standard business days reported in cell B2, an additional two to three holidays are eliminated in cells B3 and B4.
