Syntax. WORKDAY.INTL()(start_date,days,weekend,holidays)
Definition. This function returns the serial number of the date before or after an indicated number of workdays and allows the weekend days to be defined.
Arguments
start_date (required). The date that represents the start of the calculation.
days (required). The number of nonweekend and nonholiday days (workdays) before or after the start date. A positive value for the days returns a future date, and a negative value returns a past date.
weekend (optional). A number or string indicating which days are weekends and should not be treated as workdays.
holidays (optional). An array of one or more dates for work-free days to exclude from the working calendar, such as federal holidays and floating holidays.
Background. WORKDAY.INTL() has the same functionality as the WORKDAY() function with an additional feature that allows you to specify which days are weekends and therefore are not counted. You can specify a number or string for the weekend using the values in Table 7-3.
Table 7-3. Values for the WORKDAY.INTL() Function
Number | Weekend days | String |
|---|---|---|
1 (or no value) | Saturday, Sunday | 0000011 |
2 | Sunday, Monday | 1000001 |
3 | Monday, Tuesday | 1100000 |
4 | Tuesday, Wednesday | 0110000 |
5 | Wednesday, Thursday | 0011000 |
6 | Thursday, Friday | 0001100 |
7 | Friday, Saturday | 0000110 |
8 | Sunday | 0000001 |
9 | Monday | 1000000 |
10 | Tuesday | 0100000 |
11 | Wednesday | 0010000 |
12 | Thursday | 0001000 |
13 | Friday | 0000100 |
14 | Saturday | 0000010 |
The string comprises seven characters, with each character representing a day of the week. A 0 indicates a workday, and a 1 indicates a work-free day. You can use any combination except 1111111.
Example. To calculate a payment date 14 days after the current date but not on a weekend date, use
=WORKDAY.INTL(TODAY(),14)
This would return 10/19/2010 from a start date of September 9, 2010.
To calculate the payment date 10 days after a start date, use the formula
=WORKDAY.INTL("12/12/2011",10)This returns 12/26/2011, 10 working days from the start date of December 12, 2011.
When taking into consideration the Christmas holidays, the calculation
=WORKDAY.INTL("12/12/2011",10,1,{"12/25/2011";"12/26/2011"})returns 12/27/2011. And to calculate a payment date 10 days after December 4, 2011, where Monday, Saturday, and Sunday are nonworking days,
=WORKDAY.INTL("12/2/2011",10,"1000011")returns 12/21/2011. Note that in the last formula, holidays are enclosed in braces and not in parentheses.