Adding Days Exclude Specific Days of Week

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,

add days exclude certain days of week1

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

add days exclude certain days of week1

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

add days exclude certain days of week1

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

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

 add days exclude certain days of week1

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

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

add days exclude certain days of week1

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])…
Related Posts

Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example: There are two numbers “100” in range A2:A9, they are both the third largest ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Calculate Grades With VLookup in Excel

Why Should You Calculate Grades With VLookup Excel? If you're looking for a simple way to find out a student's grades, VLOOKUP Excel can do the trick. This function uses a lookup table to find the values and sort them ...

Repeating Character n Times in Excel

In daily life, we can use repeated characters to indicate the magnitude or priority of something. For example, a single “!” indicates a minor case, double “!!” indicates a medium case, and triple “!!!” indicates a high case, and so ...

Calculate Cumulative Loan Interest in Excel

What is Cumulative Loan Interest? When comparing different types of loans, many people want to know what is Cumulative Loan Interest. The sum of all interest payments you've made on a loan is referred to as cumulative interest. Different lenders ...

Sidebar