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

Data Analysis

  • Understanding Anova in Excel
  • How To Load Analysis ToolPak in Excel
  • Number and Text Filters Examples in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Move chart to a separate worksheet in Excel

References

  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel LOOKUP Function
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to use Excel MATCH Function
  • Count rows with at least n matching values

Data Validations

  • Excel Data validation only dates between
  • Excel Data validation must begin with
  • Excel Data validation whole percentage only
  • Excel Data validation unique values only
  • Excel Data validation date in specific year

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:

=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.

Worked Example:   How to calculate project start date based on end date in Excel

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:   Add workdays to date custom weekends in Excel

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

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

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

  • How to use Excel TRUE Function
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • Return blank if in Excel
  • Invoice status with nested if in Excel

Date Time

  • Convert Excel time to decimal hours in Excel
  • Check If Two Dates are same month in Excel
  • Get day name from date in Excel
  • Get month name from date in Excel
  • Get days before a date in Excel

Grouping

  • Running count group by n size in Excel
  • Group numbers with VLOOKUP in Excel
  • Group arbitrary text values in Excel
  • Calculate conditional mode with criteria in Excel
  • Group times into unequal buckets in Excel

General

  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • How to choose page/paper size in Excel before Printing
  • Currency vs Accounting Format in Excel
  • How to test a range for numbers in Excel
  • How to calculate project complete percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning