The dates in columns D and E are dynamically generated based on the start date in B5.
Conditional formatting is used to shade excluded days in gray and to highlight the final calculated dates in yellow.
All calculations are performed with the WORKDAY.INTL function.
The 7-digit code “0000111” is supplied for theweekendargument.
This is what defines workdays and non-workdays.
There is one digit for each way of the week, Monday through Saturday.
WORKDAY.INTL can optionally exclude holidays as well.
The names are for convenience only and make the formulas easier to read.
The result is Monday, December 30, 2024.
The result is Thursday, January 2, 2025.
Notice the holidays in the range B11:B13 are valid dates.
When providing holidays, only the date matters.
The name of the holiday is not relevant.
The dates are dynamically generated based on the start date with theSEQUENCE function.
This works becauseExcel datesare stored as serial numbers.
However, the dates must be formatted as dates otherwise Excel might display the raw serial numbers.
To keep this formatting in sync with the results in column G, we must use the WORKDAY.INTL function.
The formula returns TRUE, and the shading is applied.
In column E, the second formula checks for a date equal to the result in cell G6.
The difference is that the conditional formatting is unlinked from column G results.
