Exceljet

Quick, clean, and to the point

Add days exclude certain days of week

Excel formula: Add days exclude certain days of week
Generic formula 
=WORKDAY.INTL(date,days,"pattern")
Explanation 

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.

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
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables