Need to count days in Google Sheets? There are a few ways to do it, depending on what you want to find. For example, there are some methods especially for finding the number of days between dates in Google Sheets. This kind of calculation can be useful if you’re creating a schedule for your students or while creating project plans that have start and end dates.
In this tutorial, I will show you how to calculate the number of days between dates in Google Sheets, as well as the number of weekdays and the number of weekends between two dates in Google Sheets. Ready to count days? Let’s get started.
This Article Covers:
How to Calculate Days Between Dates in Google Sheets
Say you want to count days in Google Sheets. If you know the specific range you want to measure, there’s an easy way to do it. Here’s how to find Google Sheets number of days between two dates:
- Put your start date in cell A2 and your end date in cell B2
- Click an empty cell
- Type in the formula =DAYS(B2,A2). You can also use the actual dates in quotation marks eg. =DAYS(“02/01/2018”, “07/03/2018”)
- Click enter.
If you’re creating a project plan for work, it’s likely that you are interested in working days or business days only. I made a video specifically about that. Watch it below or check it out on my YouTube channel.
[adthrive-in-post-video-player video-id=”ygY9DtNs” upload-date=”2023-10-17T15:05:48.000Z” name=”How to Calculate Days Between Dates” description=”Here’s a formula that calculates days between dates in Google Sheets. It specifically allows you to exclude holidays, too.” player-type=”default” override-embed=”default”]
How to Count Days In Google Sheets
So the DAYS function helps you count days in Google. You don’t have to rely on it though. While you could use visuals from a calendar, there’s an even easier way. Google treats dates as numbers. That means you can also just use an equation. Here’s an example. Suppose you have the two dates as shown below:
The easiest way to calculate the number of days between the two specified dates is to subtract the start date from the end date.
The below formula would work in this case:
=B2-B1
Another way to calculate the same is to use the DAYS function. Like I mentioned above, it’s the quickest way to count days in Google Sheets.
The below formula would also give the same result:
=DAYS(B2,B1)
Note that the result here would not include both the start and end date in the calculation. For example, if the start date is January 1, 2018, and the end date is January 2, 2018, the result would be 1.
For example, if the start date is January 1, 2018, and the end date is January 2, 2018, the result would be 1. If you want both the start and end date included in the result, you need to add 1 back to the result.
Google Sheets Days Between Date and Today
There are plenty of date-related functions that can be used in Google Sheets to calculate days between dates. There are two simple ways to count Google Sheets days from today, and both of these methods use the DAYS Function to figure out the number of days between two dates inclusive of the current date.
The first method is to use the TODAY() function as part of your syntax like:
=DAYS(TODAY(),B1)
You must make sure to use the TODAY function as the first argument; otherwise, the Google Sheets date math programming will give the result as a minus figure.
The functions still work, and you could consider the minus figure as that many days have passed and a positive number as days are still yet to pass if the other date is in the future. It’s a good way to have Google Sheets count days from date to today.
Yet, the simplest way is just to use the TODAY functions as the first argument to have Google Sheets calculate days between a date and today. Instead of using the TODAY() Function in the syntax, you could also just add today’s date into Google Sheets to subtract dates from the current date. You’d use the same formula as you would for any other DAYS function, such as:
=DAYS(B2,B1)
You could use the TODAY() function for the cell with the current date or type it manually. The NOW function would also work for Google Sheets to count days between a date and today. This is my favorite way to find the total number of days between dates in a spreadsheet. However, it’s not the only way. There are other methods that give you more control over what counts as a workday and a holiday. You can also discount your birthday with some functions. Here are a few more methods.
Calculate the Number of Working Days Between Two Dates in Google Sheets
Now to calculate the working days, you need to exclude all the weekend days and all the holidays occurring on weekdays. There are two functions that can help you count workdays. One assumes Saturdays and Sundays are weekends. The other function allows you to define what counts as a workday.
Here are the two functions we will use to calculate the number of working days in Google Sheets:
- NETWORKDAYS function – Use this function when the weekend days are Saturday and Sunday (both included).
- NETWORKDAYS.INTL function – Use this function when the weekend days are days other than Saturday and Sunday.
Let’s first have a look at the syntax of NETWORKDAYS in Google Sheets.
NETWORKDAYS Function – Syntax & Arguments
=NETWORKDAYS(start_date, end_date, [holidays])
- start_date – a date value that represents the start date.
- end_date – a date value that represents the end date.
- [holidays] – (this is an Optional argument) This argument specifies the dates that are holidays and need to be excluded when calculating net working days. These could be public/national holidays. You can have these holidays in a range of cells and use the reference in this argument or create a named range for these cells and use the named range in the formula. You could also import a range if you so desire.
Now let’s see an example where we calculate the number of working days between two dates. You can build your own day-counting calculator in a spreadsheet. Here’s how.
Suppose you have a data set as shown below, and you want to calculate the number of working days for each activity (the result should exclude holidays and Saturdays/Sundays).
Below is the formula that will give you the total number of working days:
=NETWORKDAYS(B2,C2,$F$2:$F$5)
In most cases, the weekend days are Saturday and Sunday.
However, it may not always be the case. For example, in some countries, the weekend days are Friday and Saturday, and in some jobs, you may have a working Saturday, and only Sunday is the weekend holiday. Fortunately, there’s a solution for that. Just use the NETWORKDAYS.INTL function.
I’ll break that down next. First, let’s look at the syntax of the NETWORKDAYS.INTL function in Google Sheets.
NETWORKDAYS.INTL Function – Syntax & Arguments
NETWORKDAYS.INTL stands for Networkdays International function. It’s another way to count dates in Google Sheets. If you have a start date, end date, and weekend or holiday dates, this method counts days automatically.
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
- start_date – a date value that represents the start date.
- end_date – a date value that represents the end date.
- [weekend] – (Optional) Here, you can specify the weekend. There are two ways you can specify the weekend. It could be a string of numbers or a numeric value. If you omit this, Saturday and Sunday are taken as the weekend.
- Using String of numbers – in this function, seven days are represented by seven numbers (each number being a 0 or a 1). A ‘0’ would mean a working day a ‘1’ would mean a non-working day. So the string for a week with Saturday and Sunday as weekends (non-working days) would be “0000011”, where the first number represents Monday, the second represents Tuesday, and so on.
- Using a Number – 1 represents Saturday and Sunday as weekends, 2 represents Sunday and Monday, and weekend as so on. You can use a number between 1 to 7 for all combinations of 2 consecutive weekend days. If you only have one single day as a weekend day, use numbers from 11-17. 11 means that only Sunday is the weekend, 12 means that only Monday is the weekend and so on.
- [holidays] – (this is an Optional argument) This argument specifies the dates that are holidays and need to be excluded when calculating net working days. These could be public/national holidays. You can have these holidays in a range of cells and use the reference in this argument or create a named range for these cells and use the named range in the formula.
Now let’s see an example where we need to calculate the number of working days between two dates, and the weekend days are Friday and Saturday.
Below is the formula that will give us the number of working days for each activity:
=NETWORKDAYS.INTL(B2,C2,7,$F$2:$F$5)
Note that the third argument of the formula is 7, which is the number that represents Friday and Saturday, and weekend days.
If you want to use the string method of specifying weekends, you can use the below formula, and it will give the same result:
=NETWORKDAYS.INTL(B2,C2,"0000110",$F$2:$F$5)
Remember, the 0 in the string represents a working day, and 1 represents a non-working day. “0000110” means that Friday and Saturday are non-working days.
Calculate the Number of Days Between Dates Using the DATEDIF Function
The DATEDIF function is also used to have Google Sheets count days between dates. Like other IF functions in Google Sheets, this one adds a condition to the evaluation. That makes it helpful when you have a year, month, or date format that differs from normal.
The Syntax for the DATEDIF function is:
=DATEDIF(start_date, end_date, unit)
- start_date – The first date in the calculation to subtract from. It can be a cell reference containing a DATE, a function returning a DATE value, or the date itself in quotation marks.
- end_date: The last date in the calculation to be subtracted from. It can be a cell reference containing a DATE, a function returning a DATE value, or the date in a numeric format in quotation marks.
- unit: The abbreviation signifying the date format or unit of time. For example, “Y” is for years, “M” for months, and “d” for days.
We can use this function in our example as follows:
In this example, we’ve used the formula =DATEDIF(B2,C2,”d”)
The function calculates all the days between the start date and the end date, including the last day.
Calculate the Number of Days Between Dates Using the MINUS Function
The MINUS function is a pretty simple and convenient function that can be used to get the difference between any numerical value in Google Sheets.
It works the same with date values as it does with other numerical values to get the Google Sheets today minus the date.
The Syntax for the MINUS function is:
=MINUS(value1, value2)
- value1: The number to be subtracted from. In this case, this would be our end date.
- value2: The number to subtract from value1. In this case, this would be our start date.
Using the MINUS function in an example, this is how we would get Google Sheet days between dates.
The formula =MINUS(C2, B2) gives us the date difference between two dates. If we were to use the date format instead of cell references, then the formula would be
=MINUS(“1/24/2018”,”1/1/2018”)
Calculate the Number of Days Between Dates Using the DAYS360 Function
The DAYS360 function is much more complicated than the other functions and is commonly used in calculating fixed-income securities for a financial year.
The Syntax for the DAYS360 function is:
=DAYS360(start_date, end_date, [method])
- start_date – The first date in the calculation to subtract from. It can be a cell reference containing a DATE, a function returning a DATE value, or the date itself in quotation marks.
- end_date: The last date in the calculation to be subtracted from. It can be a cell reference containing a DATE, a function returning a DATE value, or the date itself in quotation marks.
- Method: [ OPTIONAL] – Indicates which method for day count to use. There’s the US method, which is 0 and is the default, and there’s the UK method.
The function uses the start date first, unlike the DAYS function, but the other that it can be used the same way as the DATEDIF function in our example:
In this case, we’ve used the formula:
=DAYS360(B2,C2)
Calculate the Number of Weekends Days Between Two Dates in Google Sheets
To calculate the number of weekend days between two dates, we can use the NETWORKDAYS function along with the DAYS function.
Suppose you have the data set as shown below:
Here is the formula that will give you the total number of weekend days between two specified dates in Google Sheets:
=DAYS(B2,A2)+1-NETWORKDAYS(A2,B2)
Note that here we have assumed that the weekend days are Saturday and Sunday. In case you want to calculate it for days other than Saturday and Sunday, use the NETWORKDAYS.INTL function.
Calculating the Number of Mondays Between Two Dates
Sometimes, when scheduling classes for students or creating project plans, you may want to know exactly how many Mondays, or Tuesdays, or any other specific days are there between two dates. And sometimes you’ll want to check your date formatting to make sure your team understands what’s happening.
You can use the NETWORKDAYS.INTL function to get the number of any specific day(s) in Google Sheets. The trick here is to specify only Monday as the working day and all the remaining days as the non-working days.
Here is the formula that will give the number of Mondays between two dates:
=NETWORKDAYS.INTL(A2,B2,"0111111")
Note that the string used in the formula is “0111111”. This string means that Monday is a working day, and all other days are non-working days.
Hence this formula only returns the number of Mondays.
You can use the same concept to find the number of any other weekday or combination of weekdays. A situation where this could be useful is when you want to calculate the number of working days in a part-time job (where let’s say you work only on Mondays and Wednesdays).
Download Our Example Spreadsheet
You can download our EXAMPLE SHEET to follow along with my guide on how to count dates in Google Sheets.
Wrapping Up
Now you know how to calculate the number of days between dates in Google Sheets. I hope this was useful for you! You may also be interested in our guide for using sumif between two date ranges.
You May Also Like the Following Google Sheets Tutorials: