Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Create One and Two Variable Data Tables in Excel
  • How to create Gauge Chart in Excel
  • Create Scatter Chart in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Excel Bar Chart

References

  • Find closest match in Excel
  • Left Lookup in Excel
  • How to get address of named range in Excel
  • How to use Excel ROWS Function
  • Basic INDEX MATCH approximate in Excel

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation no punctuation
  • How To Create Drop-down List in Excel
  • Excel Data validation whole percentage only
  • Excel Data validation must not contain

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:   How to calculate Next working/business day 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:   Get project end date 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:   Add workdays no weekends 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:

Number and Text Filters Examples in Excel

Next Post:

Convert date string to date time 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

Logical Functions

  • SWITCH function example in Excel
  • Invoice status with nested if in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • FALSE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get project midpoint in Excel
  • Convert date to month and year in Excel
  • Calculate years between dates in Excel
  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate nth day of year in Excel

Grouping

  • Categorize text with keywords in Excel
  • How to randomly assign data to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Group times into unequal buckets in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Find Most Frequently Occurring Word in Excel Worksheet
  • How to calculate percent change in Excel
  • How to get random value from list or table in Excel
  • Spell Check in Excel
  • How to set or clear a print area in Excel Worksheet
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning