How to Calculate Days Between Two Dates in Google Sheets

Working with date and time has become easy in Google Sheets thanks to some formulas.

You can easily calculate the number of days between two dates by simply subtracting the dates (in case you need all the days), or use formulas to find out working days or non-working days.

In this tutorial, I will cover various scenarios where you can calculate days between two dates in Google Sheets

One thing you should know about dates is that these are just numbers that have been formatted to look like dates. Since these are numbers, you can perform operations such as addition and subtraction with dates.

Let’s get started!

Calculating Total Days Between two dates

Suppose you have the start date and the end date of a project (as shown below) and you want to quickly know the total number of days between these two dates.

Dataset for calculating total number of days between two dates

Since dates are nothing but numbers, you can easily get this by subtraction the start date from the end date.

The below formula will give you the total number of days between these two dates.

=B2-B1

The above formula tells us that there are 274 days between the project start and end date.

Subtract Dates to get number of days in between

Easy right!

There is one thing you need to know when subtracting dates. The number of days given in the above example is exclusive of the start date. In case you want to include the start date in the result, you need to add 1 to the resulting data.

To get the number of days that include project start and end date both, use the below formula:

=B2-B1+1

Calculate Workdays between two dates

While it’s nice and easy to get the total number of days between two dates, in most cases, you just don’t need the total days but the workdays.

And in case, I am assuming working days to be Monday to Friday. In case the working days are different, you will need to change the formula (covered later in this tutorial).

After all, if it’s a project, you can’t expect your team to be working on weekends.. right!

Calculating the number of working days between two dates is not as straightforward as subtracting the values, but it’s not too hard (as Google Sheets has a formula for it).

Suppose you have the dataset as shown below and you want to calculate the number of working days between these two dates

Dataset for calculating total number of days between two dates

The below formula will give you the number of working days between these two dates:

=NETWORKDAYS(B1,B2)

Net working days between two dates

The NETWORKDAYS function (as the name suggests), gives you the networking days in between two dates (it takes start date and end date as the input arguments)

There are two things you need to know about the NETWORKDAYS function:

  • It includes both the start and end date when calculating the number of working days
  • It considers Saturday and Sunday as the weekend days. So it counts all the days that are from Mon-Friday.

While this may work well in most of the cases, there are two common scenarios where you may want to tweak this formula:

  1. You need to exclude public holidays from the working days
  2. Your weekends are different. For example, you may only get Sunday as the weekend and Saturday is working day for you. Or you are from countries where weekend days are Friday and Saturday (and not Saturday and Sunday)

The good news is, Google Sheets can handle all this easily.

Calculate Workdays While Excluding Public Holidays

For this to work, you need to have a list of holidays that you want to exclude.

In most companies, they provide a holiday calendar that you can use. Or if you’re a freelancer or small business, you can create this yourself too.

Below is an example of a holiday calendar that I have for 2020.

Holiday Calendar

Now when I am calculating the number of working days between two dates in Google Sheets, I don’t want to count the days that are holidays.

Thankfully, NETWORKDAYS function already has this feature built in. You can specify the range that has the holidays and it will automatically ignore these while calculating the workdays.

Suppose you have a dataset as shown below:

Dataset for calculating total number of days between two dates

The below formula will give you the number of working days between the start and end date while not counting the holidays:

=NETWORKDAYS(B1,B2,E2:E12)

Calculating working days with public holidays accounted for

This formula takes three arguments:

  • Start Date – B1 in this example
  • End Date – B2 in this example
  • The range that has the holiday dates – E2:E12 in this example

Note that NETWORKDAYS function is smart enough to adjust the holidays and weekends by itself. For example, in the below holiday dates, there are eight holidays between 1st April and 30th December.

But NETWORKDAYS function only considers six of these and two are on weekend days anyway (Easter and Independence Day). Since the NETWORKDAYS formula anyway disregard weekend days when doing the calculation, it will also make sure it doesn’t remove the days twice (when it’s a weekend as well as a holiday)

Calculate Workdays When Weekends are Not Sat and Sun

While NETWORKDAYS function automatically assumes weekend days to be Sat and Sun, this may not be the case for all.

Some of you may only have a Sunday off and some may work in countries where Friday and Saturday are off and Sunday is working.

To adjust these variations, there is another function in Google Sheets that you can use.

NETWORKDAYS.INTL – where INTL stands for international.

When you use the NETWORKDAYS.INTL function, apart from the start date, end date and the holidays, it also allows you to specify the weekend, where the weekend could be any one day of the week or any two consecutive days of the week,

So let’s say you have the below example and you want to calculate the number of working days when the weekend days are Friday and Saturday.

Dataset for calculating total number of days between two dates

The below formula will give you the result:

=NETWORKDAYS.INTL(B1,B2,7,E2:E12)

Calculate workdays between two days when wekend days are Friday and Saturday

The above formula would return 191, which is the number of days between start and end dates, with Friday and Saturday as weekend days.

This formula takes four arguments:

  • Start Date – B1 in this example
  • End Date – B2 in this example
  • Weekend – 7 in this example. This is where you specify the weekend days you want this formula to consider. Why 7, check out the table below.
  • The range that has the holiday dates – E2:E12 in this example

Why did I use 7 to make Friday and Saturday as Weekend Day?

This is how this formula has been coded. There are these numbers that specify weekend days and are pre-coded in the formula.

So when I use 7, it knows that I want the weekend days to be Friday and Saturday.

Below is a table that shows all the numbers you can use and what they mean:

Argument Weekend Days
1 Saturday/Sunday are weekend days
2 Sunday/Monday are weekend days
3 Monday/Tuesday are weekend days
4 Tuesday/Wednesday are weekend days
5 Wednesday/Thursday are weekend days
6 Thursday/Friday are weekend days
7 Friday/Saturday are weekend days
11 Sunday is the only weekend day
12 Monday is the only weekend day
13 Tuesday is the only weekend day
14 Wednesday is the only weekend day
15 Thursday is the only weekend day
16 Friday is the only weekend day
17 Saturday is the only weekend day

Based on the above table, if you only want Sunday to be considered as the weekend (with a six-day working week), you need to use 11 as the third arguments in the NETWORKDAYS.INTL function.

Calculate Workdays with Non-consecutive Off Days

Suppose you are in a job where you only work on specific days of the week (let’s say Monday, Tuesday and Thursday).

With such an arrangement, it becomes even more difficult to count the number of working days between two given dates.

Thankfully, when the NETWORKDAYS.INTL function was being formulated, this was also taken into account.

When you’re using the NETWORKDAYS.INTL function for something such as this, you need to clearly specify which days are working and which are not.

And you do that by using a series of seven consecutive numbers (where each number is either 1 or 0). These numbers represent the seven days of a week. The first number of the series represents a Monday, second represents a Tuesday and so on.

‘0’ means that it’s a working day and ‘1’ means that it’s a non-working day. So 0000011 would mean that first five days (Monday to Friday) are working and Saturday and Sunday are weekend days (or non-working days)

Now, you can create any combination of a week with a mix of working and non-working days. And these don’t have to be consecutive.

So in our example, where Monday, Tuesday and Thursday are working days, the code would be 0010111

And you can use this code in the NETWORKDAYS.INTL function as shown below:

=NETWORKDAYS.INTL(B1,B2,"0010111",E2:E12)

Calculating number of working days in a part time job

Note that the third argument is the string of numbers that represent working and non-working days. This needs to be within the double quotes

The last argument in this formula specifies the range that has the holiday dates.

Calculate all Mondays between two dates

Sometimes, you may not need to calculate the working days but the number of one specific weekday.

For example, suppose you want to calculate the number of Mondays that fall in a given date range (while also accounting for holidays).

A practical use of this could be when you want to know how many update calls will be done during the given dates if the call happens on Mondays.

Suppose you have the data set below where you want to find the number of Monday in between the start and end date:

To calculate the number of Mondays, I will be using the NETWORKDAYS.INTL function with the string on seven consecutive numbers that represent the days in the week.

This is nothing but a variation of the example where we calculate non-consecutive working days. In this example, we need to specify only Monday as the working day and rest all are the weekend days.

So the string of numbers would be “0111111”, where 0 represents a working day for Monday.

The below formula would give you the number of working Mondays between the start and end date (while also accounting for the holidays):

=NETWORKDAYS.INTL(B1,B2,"0111111",E2:E12)

Calculate number of Mondays between two dates

If you get a hang of how to use NETWORKDAYS and NETWORKDAYS.INTL functions in Google Sheets, you can easily manage situations where you have to calculate workdays between two days.

Hope you found this tutorial useful.

You may also like the following Google Sheets tutorials: