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.Download the Excel Workbook and PDF. Note: this is a .zip file please ensure your browser doesn’t change the file extension on download.
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.
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 Tuesdays, Saturdays and Sundays (“0100011”) 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:
|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|