Bonus Free eBook – Working with Date & Time in Excel

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_dateA date in a format Excel recognises known as the date-time serial number, or text
daysNumber 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

Excel WORKDAY.INTL Function 2

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.Excel WORKDAY.INTL Function 3

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 Tuesdays, Saturdays and Sundays (“0100011”) and any dates in the Holidays table:

WORKDAY.INTL list dates dynamic array

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:

WORKDAY.INTL list dates multi-cell array formula

Related Tutorials

Excel EDATE FunctionReturns a date that is the specified number of months before or after an initial supplied start date
Excel EOMONTH FunctionReturns 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 FunctionReturns a date a number of working days (excluding weekends & holidays) before or after a given start date
Excel End of Period DatesCalculate fiscal period end dates

Leave a Reply

Your email address will not be published. Required fields are marked *