When I first conceived the idea of writing a formula to highlight the next n working days in Google Sheets, I thought it’s easy using either of the two date functions – WORKDAY or WORKDAY.INTL.

But my experiment turned out to be quite an interesting one. As per my experimentation, it requires two more functions, and they are SEQUENCE or ROW and REGEXMATCH.

Between SEQUENCE and ROW, I would prefer SEQUENCE as it is easy to read in the formula.

So this tutorial is the outcome of that experiment. I hope you will enjoy it and use it in your real-life use.

Introduction

To highlight next n working days, as I have mentioned above, we can depend on WORKDAY or WORKDAY.INTL functions together with SEQUENCE and REGEXMATCH.

You May Like: How to Utilize Google Sheets Date Functions.

If SATURDAY and SUNDAY are the weekends in your country, you can use the former function (WORKDAY).

The latter function (WORKDAY.INTL) helps us to specify ‘other’ weekends (see the table at the end part of this post). As a side note, in some countries, FRIDAY and SATURDAY are at the weekends.

Why highlighting the next n working days is a useful tip?

Sometimes we commit to people/clients that we will meet the requirement within the next 3, 7, or 10 working days or something similar.

In that scenario, if we have a list containing dates, we can highlight the next n working days using the above-said functions as a combo formula in conditional formatting.

In the examples below, I am going to conditional format the next 10 working days that excluding weekends as well as public (custom specified) holidays.

Formula to Highlight Next N Working Days in Google Sheets

Today’s date as per my system is 11-12-2020 (11-December-2020). So the conditional formatting in my example is going to be based on this date.

When you apply the formula in your sheet, it will adjust as per the current day then.

Conditional Format Next 10 Working Days Excluding Saturday-Sunday Weekends

The dates to the conditional format are in A2:A20. Just ignore the values in the adjoining column range (B2:B20) as it is to make you understand the weekends and workdays.

Here I am writing the formula step-by-step. So you can grasp it quickly.

Steps

First, we should generate the dates to highlight. For that, we can use a SEQUENCE formula.

Just enter the below formula in cell C2. We will later cut and paste this formula inside the conditional format rule.

Formula # 1:

=ArrayFormula(
     WORKDAY(today(),sequence(10,1))
)

The above formula returns the days to highlight. If you want to highlight the next 3 days, change 10 inside the SEQUENCE with 3.

If you want to learn this formula, please read this post – How to Populate Sequential Dates Excluding Weekends in Google Sheets.

We must now match these dates in the date range in A2:A20. For that, we can use the REGEXMATCH formula as below.

Formula # 2:

=ArrayFormula(
     regexmatch(
        to_text($A$2:$A$20),
        textjoin("|",true,$C$2:$C$11)
     )
)

Please enter this formula in cell D2.

Please find this formula explanation here – Regexmatch Dates in Google Sheets – Single/Multiple Match.

To highlight the next 10 working dates excluding weekends, now we need to highlight the dates in A2:A20 wherever the Boolean TRUE appears in the corresponding rows in the range D2:D10.

Let’s write that conditional format rule using the C2 and D2 formulas.

Replace the range reference $C$2:$C$11 in formula # 2 with formula # 1. Then remove formula # 1 from C2.

Formula # 3:

=ArrayFormula(
     regexmatch(
        to_text($A$2:$A$20),
        textjoin("|",true,
           ArrayFormula(WORKDAY(today(),sequence(10,1)))
        )
     )
)

In conditional formatting, we only need to apply the rule to the first row in the range to highlight, and that’s the cell ID A2. So in the above formula, replace $A$2:$A$20 with A2.

Remove the dollar signs around the cell ID as we require a relative reference. So that, the A2 rule will apply to the whole range A2:A20.

Conditional format rule to highlight the next 10 working days excluding weekends in Google Sheets:

=ArrayFormula(
     regexmatch(
        to_text(A2),
        textjoin("|",true,
           ArrayFormula(WORKDAY(today(),sequence(10,1)))
        )
     )
)

How to Apply the Highlight N Days Rule in Conditional Formatting?

Please follow the below four steps.

1. Select the range A2:A20 and go to Format > Conditional Formatting.

2. In the ‘Conditional format rules’ panel, make sure that that “Apply to range” is A2:A20.

3. Under ‘Format rules’, select ‘Custom formula is’ and cut and paste the above formula in the given field.

4. Click ‘Done’.

How Can I Exclude Public Holidays in the Highlighting?

The above formula only excludes the weekends, i.e., Saturday and Sunday, in the highlighting. I don’t want to highlight 25-12-2020 as it is a public holiday due to Christmas Day 2020.

We can specify that holiday inside the formula # 1 as per the below syntax.

Syntax:-

=ArrayFormula(
     WORKDAY(today(),sequence(10,1),{public_holiday_1,public_holiday_2,public_holiday_3})
)

Since we want to exclude only one public holiday, that’s Christmas day; we can include 25-12-2020 as below in line with the above syntax.

=ArrayFormula(
     WORKDAY(today(),sequence(10,1),{date(2020,12,25)})
)

When we apply the changes in the existing conditional format rule, it would be as below.

Formula Rule that Excludes Public Holiday

=ArrayFormula(
     regexmatch(
        to_text(A2),
        textjoin("|",true,
           ArrayFormula(WORKDAY(today(),sequence(10,1),{date(2020,12,25)}))
        )
     )
)

Highlight Next 10 Working Days Excluding (International) Weekends Google Sheets

In all the above formulas, change the function WORKDAY with WORKDAY.INTL. This way we can change the weekends.

If I consider Friday-Saturday as the weekends, the changes in formula # 1 will be as below.

Earlier:

=ArrayFormula(
     WORKDAY(today(),sequence(10,1))
)

Now:

=ArrayFormula(
     WORKDAY.INTL(today(),sequence(10,1),7)
)

The number seven in the formula represents the Friday-Saturday weekends. To change the weekends in the highlighting, change that as per the table below.

In concise;

To highlight the next 10 working days excluding Friday-Saturday weekends, use the below formula.

=ArrayFormula(
     regexmatch(
        to_text(A2),
        textjoin("|",true,
           ArrayFormula(WORKDAY.INTL(today(),sequence(10,1),7))
        )
     )
)

To highlight the next 10 working days excluding Friday-Saturday weekends and the public holiday, i.e., Christmas day, use the below formula.

=ArrayFormula(
     regexmatch(
        to_text(A2),
        textjoin("|",true,
           ArrayFormula(WORKDAY.INTL(today(),sequence(10,1),7,{date(2020,12,25)}))
        )
     )
)

That’s all. Enjoy!

Sample_Sheet_111220

Resources:

• List All Sundays from a Start and End Date.
• Find Missing Sequential Dates in a List in Google Sheets [Array Formula].
• Auto-Fill Sequential Dates When Value Entered in Next Column in Google Sheets.
• Date Related Conditional Formatting Rules in Google Sheets.
• Highlight Earliest Events Based on Date Column in Google Sheets.
• How to Highlight Cells Based on Expiry Date in Google Sheets.