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):
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:
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:
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:
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:
Google Sheets Days Between Dates
The DAYS function is the easiest way to count the days between two dates in Google sheets.
- Open a google spreadsheet and click on an empty cell
- Type =DAYS("01/01/2022","01/01/2021"), replacing the example dates with your own dates
- Hit enter and the number of days between your dates will be displayed.
* Make sure you enter your dates in reverse order with end date first and start date second.
Sample Sheet
You can check the sample sheet below to check the functions:
Supercharge Your Spreadsheets with Lido
Recommended articles
More resources
