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

Lookup and Reference Examples

  • How to use Excel COLUMN Function
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Excel Advanced Lookup using Index and Match Functions
  • How to get address of last cell in range in Excel

Data Analysis Examples

  • How To Perform and Interpret Regression Analysis in Excel
  • Understanding Anova in Excel
  • How to add Trendline to a chart in Excel
  • How to perform a t-Test in Excel
  • Excel Bar Chart

Data Validation Examples

  • Prevent invalid data entering in specific cells
  • Excel Data validation number multiple 100
  • Excel Data validation must not contain
  • Excel Data validation allow weekday only
  • Excel Data validation exists in list

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:

Worked Example:   Get project end date in Excel
=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.

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:   Get project midpoint in Excel

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

Worked Example:   Add workdays to date custom weekends in Excel

Post navigation

Previous Post:

Add workdays to date custom weekends in Excel

Next Post:

Add months 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
  • Split numbers from units of measure in Excel
  • Remove line breaks in a cell in Excel
  • How to use double quotes inside a formula in Excel
  • UPPER function: Description, Usage, Syntax, Examples and Explanation
  • How to Capitalize first letter in a sentence in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Convert date to text in Excel
  • Add years to date in Excel
  • WEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Get week number from date in Excel
  • Calculate periods for annuity in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINTM function: Description, Usage, Syntax, Examples and Explanation
  • Example of payment for annuity in Excel
  • How to calculate simple interest in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles