說明
Use this function to determine a date that is a given number of working days in the future or past from a specified start date.
You can also specify both which days are to be considered weekend (non-working) days and the holidays to be excluded. Unlike WORKDAY, WORKDAY.INTL can be configured for a custom workweek, where any day of the week can be a workday or non-workday.
This function is useful for calculating start dates, delivery dates, and completion dates that need to factor in both working and non-working days.
語法
WORKDAY.INTL(start_date,days,[holidays])
Inputs
This function has the following arguments:
| 名稱 | 必要 | Valid input |
|---|---|---|
start_date |
是 | Date to be used as the base for calculation. This can be a cell reference, an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD. A formula which results in one of these is also acceptable. Quotation marks are required. |
天 |
是 | Working days before or after start_date. This can be a cell reference, an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD.A formula which results in one of these is also acceptable. Quotation marks are required. |
weekend |
沒有 | Which days of the week are treated as working and non-working days. If weekend is omitted, WORKDAY.INTL will treat Saturdays and Sundays as non-working days. |
holidays |
沒有 | A list of non-work days as supplied as dates. This can be a cell reference, a cell range (D2:D13), an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD. A formula which results in one of these is also acceptable. If holidays are not provided, WORKDAY.INTL will treat only Saturdays and Sundays as non-working days. |
範例
Configuring weekends
The WORKDAY.INTL function provides two options to configure weekends. The first option is to supply a number as shown in the table below (8, 9, and 10 aren't supported).
| Weekend days | Weekend number |
|---|---|
| Saturday, Sunday | 1 (default) |
| Sunday, Monday | 2 |
| Monday, Tuesday | 3 |
| Tuesday, Wednesday | 4 |
| Wednesday, Thursday | 5 |
| Thursday, Friday | 6 |
| Friday, Saturday | 7 |
| Sunday only | 11 |
| Monday only | 12 |
| Tuesday only | 13 |
| Wednesday only | 14 |
| Thursday only | 15 |
| Friday only | 16 |
| Saturday only | 17 |
The second way to configure weekends is to provide a 7 character text string enclosed in quotation marks composed of 1s and 0s, where "1" indicates a non-work day and "0" indicates a work day. The first position represents Monday. Example: "1100000" is a week where Monday and Tuesday are non-work days.
範例資料
| A (start date) |
B (days) |
C (weekend) |
D (holidays) |
WORKDAY.INTL result |
|---|---|---|---|---|
| 1/1/2024 | 24 | 2/15/2024 | 02/12/2024 | |
| 1/1/2024 | 12 | 2 | 2/28/2024 | 1/17/2024 |
| 1/1/2024 | -12 | 5 | 7/4/2024 | 12/16/2023 |
| 7/19/2003 | -90007 | 11 | 7/5/2024 | #NUM! |
| 41631 | 120 | 0001111 |
9/6/2024 | 09/29/2014 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=WORKDAY.INTL(A1,B1) |
Determines the date that is the number of workdays cell B1 days after cell A1 date, with no holidays specified. | 2/12/2024 |
=WORKDAY.INTL(A2,B2,C2) |
Determines the date that is the number of workdays cell B2 days after cell A2 date, using cell C2 for the weekend day pattern. | 01/17/2024 |
=WORKDAY.INTL(A1,B1,1,D1:D3) |
Determines the date that is the number of workdays cell B1 days after cell A1 date, using "1" for the weekend day pattern, and the dates in cells D1:D3 as holidays. |
2/12/2024 |
=WORKDAY.INTL("1/1/2024",B1,"0001111") |
Determines the date that is the number of workdays cell B1 days after January 1st, 2024, using "0001111" for the weekend day pattern. | 3/11/2024 |
=WORKDAY.INTL(DATE(2024,3,12),-16,4) |
Determines the date that is the number of workdays 16 days before March 12th, 2024, using "4" for the weekend day pattern. | 2/19/2024 |
=WORKDAY.INTL(A5,B5,"0001111",D5) |
Determines the date that is the number of workdays cell B5 days after cell A1 date, using "0001111" for the weekend day pattern, and the date in cell D5 as a holiday. | 09/29/2014 |
附註
- WORKDAY.INTL doesn't implicitly know which days are holidays. These have to be specified by the user.
- WORKDAY.INTL doesn't include the start date as a workday.
- WORKDAY.INTL automatically excludes all Saturdays and Sundays.
- If
daysis "0", WORKDAY.INTL returns the value ofstart_dateunchanged. - WORKDAY.INTL ignores any time values.
- If any of the provided date(s) aren't valid, WORKDAY.INTL returns the #NUM! error.
- Wildcards don't work with this function.
提示
- It's generally easier to put holiday dates in a cell range and reference that range.
Related functions
DATE
DATEVALUE
DAY
DAYS
DAYS360
EDATE
EOMONTH
MONTH
NETWORKDAYS
NETWORKDAYS.INTL
WEEKDAY
WORKDAY
WORKDAY.INTL
YEAR
YEARFRAC