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 ROWS Function
  • How to get relative row numbers in a range in Excel
  • Perform case-sensitive Lookup in Excel
  • Count rows that contain specific values in Excel
  • Lookup entire row in Excel

Data Analysis Examples

  • How to Create One and Two Variable Data Tables in Excel
  • How To Load Analysis ToolPak in Excel
  • How To Create Frequency Distribution in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How to create running total in an Excel Table

Data Validation Examples

  • Excel Data validation whole percentage only
  • Excel Data validation date in specific year
  • Excel Data validation require unique number
  • Prevent invalid data entering in specific cells
  • Excel Data validation no punctuation

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:   Count Errors 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:   How to test for all values in a range are at least 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:   Get last working day in month 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
  • FIND, FINDB functions: Description, Usage, Syntax, Examples and Explanation
  • Remove unwanted characters in Excel
  • Remove file extension from filename in Excel
  • How to check if cell contains some words but not others in Excel
  • Remove text by position in a cell in Excel
  • Assign points based on late time in Excel
  • Custom weekday abbreviation in Excel
  • Add workdays to date custom weekends in Excel
  • How to enter Today’s Date or Static Date and Time in Excel
  • Extract date from a date and time in Excel
  • ACCRINTM function: Description, Usage, Syntax, Examples and Explanation
  • Example of Future value of annuity in Excel
  • MIRR function: Description, Usage, Syntax, Examples and Explanation
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
  • ODDLYIELD function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles