The Excel WORKDAY.INTL function (new in Excel 2010) returns a date serial number that is the specified number of working days before or after the start date. Working days exclude weekends, which can be customized, and any dates identified as holidays.
WORKDAY.INTL is handy when calculating due dates that exclude weekends or holidays where your weekend days aren't Saturday and Sunday. And with a little known trick it can also generate a list of dates.
Watch the Video
Bonus Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel, including all Date functions explained with examples - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the sample workbook.
Download the Excel Workbook and PDF. Note: This is a zip file including an Excel workbook with detailed instructions and a PDF version for your reference.
Excel WORKDAY.INTL Function Syntax
| Syntax: | =WORKDAY.INTL(start_date, days, [weekend], [holidays]) |
| start_date | A date in a format Excel recognises known as the date-time serial number, or text |
| days | Number of non-weekend and non-holidays to add or subtract |
| [weekend] | Optional - choose which days of the week are not working days (see list below). If omitted, the default is Saturday and Sunday. |
| [holidays] | Optional list of holidays to exclude |
Excel WORKDAY.INTL Function Examples
Note 1: Only the date portion of a date-time serial number is used by WORKDAY.INTL. Any time element is ignored.
Note 2: Only whole numbers are recognised by the 'days' argument. e.g. 1.7 days would be rounded down to 1 day.
Bonus Tip: The weekend argument can be stipulated using a text string of 7 characters, with each character representing a day, starting with Monday. A 1 represents a non-working day and a 0 represents a workday. See examples below which stipulate non-workdays for Thursday, Friday, Saturday and Sunday.
The text string can be placed in a cell e.g. D50 or directly in the formula, see cell E51.
List Dates
Another use for WORKDAY.INTL is to generate a list of dates. The process differs depending on whether you have dynamic array functions or not.
Generate a List of Dates - Dynamic Arrays
The formula below generates a list of 10 dates (using SEQUENCE(10) ), starting on 1st January 2022. It skips Mondays, Saturdays and Sundays ("1000011") and any dates in the Holidays table:
Generate a List of Dates - No Dynamic Arrays
For those with Excel 2019 or earlier, you can use the multi-cell array formula below instead. Note: you must select the 10 cells before writing the formula, then enter the formula with CTRL+SHIFT+ENTER:
Related Tutorials
| Excel EDATE Function | Returns a date that is the specified number of months before or after an initial supplied start date |
| Excel EOMONTH Function | Returns a date that is the last day of the month that is a specified number of months before or after the date serial number |
| Excel WORKDAY Function | Returns a date a number of working days (excluding weekends & holidays) before or after a given start date |
| Excel End of Period Dates | Calculate fiscal period end dates |