Add days exclude certain days of week in Excel
It you need to add days and exclude certain days of week in Excel then this tutorials is for you.
To add days to a date while excluding specific days (i.e. exclude Tuesdays and Thursdays, Wednesdays only, etc.) you can use the WORKDAY.INTL function with a special pattern code see the example below:
Formula
=WORKDAY.INTL(date,days,"pattern")
Explanation
In the example shown, the formula in C7 is:
=WORKDAY.INTL(B7,7,"0101000")
This formula adds 7 days to the date in B7, excluding Tuesdays and Thursdays.
How this formula works
The WORKDAY.INTL function is based on the WORKDAY function, which is designed to add work days to a date. WORKDAY automatically excludes Saturday and Sunday, and optionally can exclude a list of custom holidays. The WORKDAY.INTL does the same thing, but makes it possible to exclude any days of the week, in addition to holidays.
To exclude specific days of the week you can either use a pre-configured code (see this page for a full list of presets) or provide your own “pattern code”. The pattern code must be 7 digits long and have either a zero for each day of the week, starting on Monday and ending on Sunday. Values equal to 1 are excluded, and days with zero values are treated normally.
So, assuming you want to add 7 days to a date in cell A1, you can write formulas like this:
=WORKDAY.INTL(A1,7,"0000011") // exclude Sat, Sun =WORKDAY.INTL(A1,7,"0010011") // exclude Sat, Sun, Wed =WORKDAY.INTL(A1,7,"0101011") // exclude Sat, Sun, Tue, Thu