As a MS Excel user, you might have come across a situation where you need to add days into the date by excluding specific days of the week. You might have done this kind of task manually, which is an acceptable way to add days into a few dates by excluding specific days. But it is an impractical way to add days to many dates and exclude specific days.

But don’t be worry about it because by reading this article carefully, you would get to know a formula to add days into different dates and exclude certain days of the week.

So let’s dive into it,

General Method:

 The formula given below will add days to date while omitting certain days (i.e., excluding Thursdays and Tuesdays and, Friday only, etc.)

=WORKDAY.INTL(Date_value,Certain_Days,Pattern_code)

Explanation of The Syntax:

 Before going into the explanation that how to get this formula into use, let’s first know about the usage of the different syntax used in this formula to understand it completely.

•  WORKDAY.INTL: The WORKDAY.INTL function lets you personalize the weekends according to your need (any day of the week).
• Date: This value represents the input values.
• Days: This determines the number of days to be computed.
• Pattern: The pattern code must be 7 digits long and contain 0s for including days and 1s for excluding days. It begins on Monday and concludes on Sunday.
• Comma symbol (,): This symbol acts like a separator that aids in separating a list of values.
• Parenthesis (): This primary function of this symbol is to group the elements.

Let’s See How This Formula Works:

 For example, If you got a task that requires you to add days to date while excluding specified days (i.e., excluding Thursdays and Tuesdays and, Friday alone, etc.), you may use the WORKDAY.INTL method with a custom pattern code.

The formula in F2 in the case provided is:

=WORKDAY.INTL(A2,10,"0101100")

The above formula would add the 10 days to the date in F7, except for Tuesdays,Thursdays and Fridays.

The WORKDAY.INTLfunction is based on the WORKDAY function, adding workdays to a date. WORKDAY eliminates Saturday and Sunday by default and can alternatively exclude a list of custom holidays. The WORKDAY.INTL function performs the same thing but allows you to omit any day of the week in addition to holidays.

You may either use a pre-configured code (see this page for a complete list of presets) or enter your own “pattern code” to exclude particular days of the week. The pattern code should be 7 digits long and contain either a zero or a letter for each day of the week; it must begin on Monday and end on Sunday. Days with a value of 1 are omitted, whereas days with a value of 0 are treated normally.

So, if you want to add 10 days to a date in cell A2, you may use the following formula:

For omitting Saturday and Sunday, we would write the pattern as follows:

=WORKDAY.INTL(A2,10,"0000011")

For omitting Saturday, Sunday, and Wednesday, we would write the pattern as follows:

=WORKDAY.INTL(A2,10,"0010011")

 

For omitting Saturday, Sunday, Tuesday, and Thursday, we would write the pattern as follows:

=WORKDAY.INTL(A2,10,"0101011")

Related Functions

• Excel WORKDAY function
  The Excel WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified.The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…
• Excel WORKDAY.INTL function
  The Excel WORKDAY.INTL function returns the serial number of the date before or after a specified number of workdays.The syntax of the WORKDAY.INTL function is as below:= WORKDAY.INTL (start_date, days, [weekend], [holidays])…