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 LOOKUP Function
  • How to retrieve first match between two ranges in Excel
  • Vlookup Examples in Excel
  • How to use Excel MATCH Function
  • Merge tables with VLOOKUP in Excel

Data Analysis Examples

  • How to create Checklist in Excel
  • How to Create Column Chart in Excel
  • Understanding Pivot Tables in Excel
  • How To Filter Data in Excel
  • How to calculate current stock or inventory in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation must not contain
  • Data validation must not exist in list
  • Excel Data validation specific characters only
  • How To Create Drop-down List in Excel

Calculate series of dates by workdays in Excel

by

This tutorials covers how to generate series of dates increment by workdays from a single start date in Excel

If need to generate a dynamic series of dates that are workdays only (i.e. Monday through Friday), you can do so with a simple formula that uses the the WORKDAY function

Formula

=WORKDAY(date,1)

Explanation

 

In the example, B6 is the hard-coded start date and the formula in B7 is:

Worked Example:   Highlight row and column intersection exact match in Excel
=WORKDAY(B6,1)

Excel solves this formula by using the WORKDAY function to return the next workday. WORKDAY is programmed to skip forward as needed to the next business day.

IF you need to take into account holidays, you add the optional argument to the WORKDAY function for  holidays:

=WORKDAY(date,1,holidays)

Where holidays is a reference to a list of dates that represent non-working days.

Worked Example:   Running count group by n size in Excel

Custom weekends

If you need take into account custom weekends (i.e. weekends are Saturday only, Sunday and Monday, etc.) you’ll need to switch to the more robust WORKDAY.INTL function, which allows you to set what days of the week are considered are considered weekends, by supplying a weekend argument in the form of a numeric code. See WORKDAY.INTL for more details.

Worked Example:   Smaller of two values in Excel

Another way

You can also generate a series of workdays with a formula that uses the WEEKDAY function. In this case, the formula tests the start date to see if is a Saturday or not. If so, 3 days are added to the start date. If not, just one day is added.

=IF(WEEKDAY(date)=6,date+3,date+1)

Post navigation

Previous Post:

How to generate series of dates increment by 1 year from a single start date in Excel

Next Post:

Generate series of dates by weekends 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
  • SUBSTITUTE function: Description, Usage, Syntax, Examples and Explanation
  • How to count total characters in a range in Excel
  • How to count total words in a range in Excel
  • How to get last word in a cell in Excel
  • LEN, LENB functions: Description, Usage, Syntax, Examples and Explanation
  • Convert date to text in Excel
  • Get days, hours, and minutes between dates in Excel
  • How to calculate Quarter of Date in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Series of dates by day
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
  • PV function: Description, Usage, Syntax, Examples and Explanation
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • DDB function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles