Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get address of first cell in range in Excel
  • How to use Excel VLOOKUP Function
  • Extract all partial matches in Excel
  • How to get relative column numbers in a range in Excel
  • How to use Excel MATCH Function

Data Analysis Examples

  • How To Sort One Column or Multiple Columns in Excel
  • Reverse List in Excel
  • Remove Duplicates Example in Excel
  • How To Compare Two Lists in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel

Data Validation Examples

  • Excel Data validation no punctuation
  • Excel Data validation date in next 30 days
  • Excel Data validation don’t exceed total
  • Prevent invalid data entering in specific cells
  • Excel Data validation with conditional list

Add days exclude certain days of week in Excel

by

It you need to add days and exclude certain days of week in Excel then this tutorials is for you.

To add days to a date while excluding specific days (i.e. exclude Tuesdays and Thursdays, Wednesdays only, etc.) you can use the WORKDAY.INTL function with a special pattern code see the example below:

Formula

=WORKDAY.INTL(date,days,"pattern")

Explanation

In the example shown, the formula in C7 is:

=WORKDAY.INTL(B7,7,"0101000")

This formula adds 7 days to the date in B7, excluding Tuesdays and Thursdays.

Worked Example:   Add workdays to date custom weekends in Excel

How this formula works

The WORKDAY.INTL function is based on the WORKDAY function, which is designed to add work days to a date. WORKDAY automatically excludes Saturday and Sunday, and optionally can exclude a list of custom holidays. The WORKDAY.INTL does the same thing, but makes it possible to exclude any days of the week, in addition to holidays.

Worked Example:   How to calculate future date say 6 months ahead in Excel

To exclude specific days of the week you can either use a pre-configured code (see this page for a full list of presets) or provide your own “pattern code”. The pattern code must be 7 digits long and have either a zero for each day of the week, starting on Monday and ending on Sunday.  Values equal to 1 are excluded, and days with zero values are treated normally.

Worked Example:   Get project end date in Excel

So, assuming you want to add 7 days to a date in cell A1, you can write formulas like this:

=WORKDAY.INTL(A1,7,"0000011") // exclude Sat, Sun
=WORKDAY.INTL(A1,7,"0010011") // exclude Sat, Sun, Wed
=WORKDAY.INTL(A1,7,"0101011") // exclude Sat, Sun, Tue, Thu

Post navigation

Previous Post:

Add days to date in Excel

Next Post:

Add business days to date in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to count total words in a range in Excel
  • How to get top level domain (TLD) in Excel
  • Get first name from name with comma — Manipulating NAMES in Excel
  • UPPER function: Description, Usage, Syntax, Examples and Explanation
  • How to abbreviate names or words in Excel
  • Convert decimal minutes to Excel time
  • Basic Overtime Calculation Formula in Excel
  • How to calculate most recent day of week in Excel
  • Display Date is workday in Excel
  • How to get number of days, weeks, months or years between two dates in Excel
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment for a loan in Excel
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles