Exceljet

Quick, clean, and to the point

Add workdays no weekends

Excel formula: Add workdays no weekends
Generic formula 
=WORKDAY.INTL(start_date,days,"0000000", holidays)
Explanation 

To add or subtract workdays days to a date, respecting holidays but assuming a 7-day workweek, you can you can use the WORKDAY.INTL function. In the example, the formula in D6 is:

=WORKDAY.INTL(B6,C6,"0000000",holidays)

This formula adds 7 workdays days to Tuesday, Dec 22. Three holidays are supplied using the named range "holidays" (B9:B11) and weekends are set using the special syntax "0000000" which means all days in a week are workdays. The result is Thu, December 31, 2015.

How this formula works

The WORKDAY.INTL can calculate a date in the future or past that respects holidays and weekends. To specify which days are considered weekends, you can use a special code (full list of codes here) or use a "mask" to indicate weekends with ones and zeros.  The mask method is more flexible, since it allows you to designate any day of the week as a weekend (i.e. non-working day). For example:

=WORKDAY.INTL(A1,3,"0000000") // no weekends
=WORKDAY.INTL(A1,3,"1000000") // weekend = Mon
=WORKDAY.INTL(A1,3,"1100000") // weekend = Mon+Tue
=WORKDAY.INTL(A1,3,"1110000") // weekend = Mon+Tue+Wed

The weekend argument is supplied as 7 characters that represent Monday-Sunday. Use one (1) to indicate weekend, and zero (0) to indicate a working day.

Since we want all days of the week to be considered working days, we use "0000000".

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.