A few days back, one of my readers requested a formula to return all the working dates between two dates in Google Sheets.

He just wanted to exclude the weekends and list all the working dates between a start date and an end date.

I took it one step further!

Other than just generating a list of dates as above, I’ll let you know how to exclude specific (local/national/international) holidays from the list.

Formula to List All the Working Dates Between Two Dates in Google Sheets

In one of my recent posts, How to Populate Sequential Dates Excluding Weekends in Google Sheets, I have explained part of the solution to this problem.

The formula there returns a specified number of working dates from a start date. The end date was not part of that formula.

There, I have already explained how we can use WORKDAY.INTL function for that.

Here is a recap.

The following start date is in cell C2, and the end date is in cell C3.

Start Date: 15/12/2019

End Date: 02/01/2020

I am not considering the end date for the time being.

The following formula lists five working dates (‘n’ dates) from 16/12/2019 to 20/12/2019.

=ArrayFormula(WORKDAY.INTL(C2,sequence(5,1),1))

How to Specify the End Date in the formula?

The above array formula will return five working dates from the start date. The number of working dates is controlled by sequence(5.

That means we can control the end date by modifying the Sequence portion of the above formula.

To know how the above WORKDAY.INTL formula works; you may please check the above-mentioned post/tutorial.

In this post, you can learn how to return all the working dates between two dates, i.e., a start and end date, in Google Sheets.

That means, how to specify an end date in the above formula?

Before starting, you should know which dates to exclude as weekend holidays from the list of working dates between the two dates above.

I have used weekend number 1 to exclude Saturday and Sunday in the above formula (please see the bold part).

Define Workweek in Formula

Here in our country, the workweek is five days from Monday to Friday. The number representing this (weekend number) is 1.

In your country (or business), the workweek may be five days or even six days (working Saturday through Thursday). That means the weekends are different.

If so, you can find the corresponding weekend number to use from here – How to Utilise Google Sheets Date Functions.

In that post, please jump to the function NETWORKDAYS.INTL to get the weekend number that you want. I am not repeating the same here.

That’s is essential to return all the working dates between two dates (a start and end date) in Google Sheets.

We will use here the same function, i.e., NETWORKDAYS.INTL to include the end date in the above formula.

The Role of NETWORKDAYS.INTL in Sequence

With the NETWORKDAYS.INTL function, we can find the date difference of two dates, not just the date difference!

For calculating the date difference, there are other functions, and they are DAYS and DATEDIF.

=DAYS(C3,C2)
=DATEDIF(C2,C3,"D")
=C3-C2

The above formulas will return the date difference of the two dates in cells C2 and C3, i.e., 18.

What we want is the total number of working days between the dates in cells C2 and C3.

We can find that using the NETWORKDAYS.INTL function in Google Sheets.

=NETWORKDAYS.INTL(C2,C3,1)

The above formula will return 14.

Explanation:

C2 – start date
C3 – end date
,1) – weekend number (Saturday and Sunday)

Formula to List/Generate All the Working Dates Between Two Dates in Google Sheets:-

I am going to use the NETWORKDAYS.INTL function in the sequence part of the formula above.

Change this formula which returns 5 (or you can say ‘n’) working dates from the start date (please refer to the screenshot above);

=ArrayFormula(WORKDAY.INTL(C2,sequence(5,1),1))

– to a formula that returns a certain number of working dates from a start date.

A certain number of working dates means the total working dates between the two dates (start date and end date).

=ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1),1),1))

Note:-

Use the same weekend number in WORKDAY.INTL as well as NETWORKDAYS.INTL functions.

UPDATE 1 on 20-10-2021:

Sometimes the above formula may return 1-2 additional working days at the end of the list. To sort out that, we can use the QUERY function.

=query(ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1),1),1)),"Select * where Col1 <date '"&TEXT(C3,"yyyy-mm-dd")&"'")

Working Dates Between Two Dates and Excluding Specific Holidays

The above formula simply excludes the specified weekends from the generated dates.

What about excluding specific (local/national/international) holidays from the list?

With an extra holiday column, we can manage that.

Steps:

Enter the holidays as a list and refer to that list in the formula.

I have entered two holidays in cell D2 and D3 that are 25/12/2019 (X’ mas) and 01/01/2020 (New Year’s day).

How to exclude these two holidays from the generated working dates from 15/12/2019 to 02/01/2020?

If you check the syntax of the functions WORKDAY.INTL and NETWORKDAYS.INTL, you can see that these functions have optional arguments to exclude holidays.

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

See how I am utilizing those optional arguments in my formula by specifying D2:D5 as the holidays.

Formula to List All Working Dates Between Two Dates Excluding Specified Holidays in Google Sheets

=ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1,D2:D3),1),1,D2:D3))

UPDATE 2 on 20-10-2021:

Here also we should modify the above formula using Query.

=query(ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1,D2:D3),1),1,D2:D3)),"Select * where Col1 <date '"&TEXT(C3,"yyyy-mm-dd")&"'")

That’s all. Enjoy!

Resources

• Array Formula to Lookup Date Between Two Dates in Google Sheets.
• Find Number of Months and Days between Two Dates in Google Sheets.
• Elapsed Days and Time Between Two Dates in Google Sheets.
• Count Values Between Two Dates in Google Sheets.
• Find Number of Working and Non-Working Days in Google Sheets.
• How to Highlight Next N Working Days in Google Sheets.