Add workdays to date custom workweek

In this example, the goal is to calculate a workday n days in the future based on a 4-day workweek and, optionally, holidays. For convenience, start (B5), days (B8), and holidays (B11:B13) are named ranges. 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 WORKDAY.INTL function

Excel's WORKDAY.INTL function takes a date and returns the next working day n days in the future or past. Unlike the simpler WORKDAY function, WORKDAY.INTL can be configured to make any day of the week a workday or a non-workday. You can use the WORKDAY.INTL function to calculate ship dates, delivery dates, and completion dates that must be calculated based on workdays. For example, with a start date in cell A1, you can calculate a date 4 workdays in the future based on a 4-day workweek (Mon-Thu) like this:

=WORKDAY.INTL(A1,4,"0000111")

In this configuration, WORKDAY.INTL will exclude Friday, Saturday, and Sunday, and return the next workday 4 days after the starting date. The 7-digit code "0000111" is supplied for the weekend argument. This is what defines workdays and non-workdays. There is one digit for each way of the week, Monday through Saturday. In this scheme, a 1 indicates a weekend (non-working) day, and a 0 indicates a workday. The code "0000111" means Monday through Thursday are workdays, and Friday through Sunday are weekends (i.e. non-working days).

WORKDAY.INTL can optionally exclude holidays as well. For example, with a list of holiday dates in the range C1:C3, you can calculate the next workday 4 days after the starting date in A1 while also excluding the dates in C1:C3:

=WORKDAY.INTL(A1,4,"0000111",C1:C3)

WORKDAY.INTL solution

Now that we have the basics out of the way, let's look at how the formulas in the example shown are configured. The worksheet in the example shown contains 3 named ranges: start (B5), days (B8), and holidays (B11:B13). The names are for convenience only and make the formulas easier to read. The formula in cell G5 calculates the next working day 4 days after December 23, 2024, and does not take into account holidays:

=WORKDAY.INTL(start,days,"0000111") // returns 30-Dec-2024

The weekend code "0000111" defines a 4-day workweek, Monday through Thursday. The result is Monday, December 30, 2024. The formula in cell G6 performs the same calculation but also excludes the dates in holidays (B11:B13).

=WORKDAY.INTL(start,days,"0000111",holidays) // returns 02-Jan-2025

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.

Data visualization

To help visualize what WORKDAY.INTL is actually doing, the worksheet also includes a range of dates in columns D and E. This part of the worksheet is not required in this solution; it exists only to help visualize how the WORKDAY.INTL function evaluates working and non-working days and arrives at a final result. The dates are dynamically generated based on the start date with the SEQUENCE function. The formulas in D5 and E5 are identical and look like this:

=SEQUENCE(13,1,start)

• rows - 13 (this number is arbitrary and can be increased or decreased)
• columns - 1
• start - start (cell B5, which contains 23-Dec-2024)

With this configuration, SEQUENCE generates 13 dates, starting with the start date in cell B5. This works because Excel dates are stored as serial numbers. However, the dates must be formatted as dates otherwise Excel might display the raw serial numbers. To make it easy to see what day of the week each date is, the dates in the range D5:E17 are formatted with the following custom number format:

ddd, dd-mmm-yy

This date format displays an abbreviated day name (e.g., "Mon") followed by the date.

Shading non-working days with conditional formatting

Part of the visualization involves shading non-working days in gray, which is done with conditional formatting triggered by a formula. To keep this formatting in sync with the results in column G, we must use the WORKDAY.INTL function. In column D, the formula used to trigger the conditional formatting looks like this:

=WORKDAY.INTL(D5-1,1,"0000111")<>D5

Essentially, we are testing for non-work days by comparing the output from WORKDAY.INTL to the original date. The formula is slightly convoluted because we can't test the date directly, we need to use a workaround formula that first subtracts 1 day from the start date and then asks for the next workday. If the result from WORKDAY.INTL is not equal to the original date, it means WORKDAY.INTL shifted the date because it is a non-working day. The formula returns TRUE, and the shading is applied.

To shade non-working days in column E, we use a similar formula that also includes our list of holidays in B11:B13:

=WORKDAY.INTL(E5-1,1,"0000111",holidays)<>E5

The result is that more days are shaded since both weekends and holidays are non-working days.

Highlighting final results with conditional formatting

Finally, we have the two formulas that highlight in yellow the calculated dates in column G:

=D5=$G$5 // column D
=E5=$G$6 // column E

In column D, the first formula checks for a date equal to the result in G5. In column E, the second formula checks for a date equal to the result in cell G6. Because G5 and G6 are not defined as named ranges, we must use the absolute references $G$5 and $G$6.

To create independent conditional formatting rules that do not require the dates in column G, you can use self-contained formulas that use WORKDAY.INTL directly like this:

=D5=WORKDAY.INTL(start,days,"0000111")
=E5=WORKDAY.INTL(start,days,"0000111",holidays)

The result is the same. The difference is that the conditional formatting is unlinked from column G results.

Conditional formatting summary

There are four conditional formatting rules used in the workbook shown as seen below:

Add workdays  - Sunday only weekends

By default, the WORKDAY.INTL function will exclude "normal" weekends where Saturday and Sunday are non-working days. To specify a custom workweek where only Sunday is a weekend day, you can use the weekend code "0000001". With this adjustment, the formulas in cells G5 and G6 are:

=WORKDAY.INTL(start,days,"0000001")
=WORKDAY.INTL(start,days,"0000001",holidays)

The formulas that trigger the conditional formatting that shades non-working days columns D and E look like this:

=WORKDAY.INTL(D5-1,1,"0000001")<>D5
=WORKDAY.INTL(E5-1,1,"0000001",holidays)<>E5

Add workdays - no weekends

In the worksheet below we have configured the WORKDAY.INTL formulas to define a custom workweek with no weekends. The formulas in cells G5 and G6 are:

=WORKDAY.INTL(start,days,"0000000")
=WORKDAY.INTL(start,days,"0000000",holidays)

The code "0000000" for the weekend argument means no days of the week are weekends (i.e. non-working days). The formulas that trigger the conditional formatting rules that shade non-working days columns D and E are:

=WORKDAY.INTL(D5-1,1,"0000000")<>D5
=WORKDAY.INTL(E5-1,1,"0000000",holidays)<>E5