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. But I decided to take it one step further! In addition to generating a list of dates, I will also show you how to exclude specific holidays from the list.
Formula to List All the Working Dates Between Two Dates in Google Sheets
In a previous post, I explained how to populate sequential dates excluding weekends in Google Sheets. In that post, I used the WORKDAY.INTL function to return a specified number of working dates from a start date. However, the end date was not part of that formula.
To include the end date in the formula, we need to modify the sequence portion of the formula. By using the NETWORKDAYS.INTL function, we can find the total number of working days between the two dates. We can then use this number in the sequence part of the formula.
Here’s how the formula looks:
=ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1),1),1))Make sure to use the same weekend number in both the WORKDAY.INTL and NETWORKDAYS.INTL functions.
Working Dates Between Two Dates and Excluding Specific Holidays
The above formula excludes weekends from the generated dates, but what about excluding specific holidays? With an extra holiday column, we can manage that as well.
Here are the steps:
- Enter the holidays as a list in a separate column.
- Refer to that list in the formula using the optional “holidays” argument in the WORKDAY.INTL and NETWORKDAYS.INTL functions.
The modified formula to exclude holidays looks like this:
=ArrayFormula(WORKDAY.INTL(C2,sequence(NETWORKDAYS.INTL(C2,C3,1,D2:D3),1),1,D2:D3))Again, make sure to use the same weekend number in both functions and refer to the holiday list in the formula.
That’s it! Now you can easily return all the working dates between two dates in Google Sheets, excluding weekends and specific holidays. Enjoy!
For more resources on working with dates in Google Sheets, check out the following articles:
- 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
To learn more about Google Sheets and enhance your productivity, visit Crawlan.com.
