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:


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.

Generic formula

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


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".

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.