Add workdays no weekends in Excel

by

It you need to add workdays no weekends in Excel then this tutorials is for you. See example below:

Therefore, 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.

Formula

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

Explanation

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.

Worked Example:   How to calculate Next working/business day in Excel

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.

Worked Example:   WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation

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

Worked Example:   Add days exclude certain days of week in Excel

Leave a Reply

Your email address will not be published.