Description
Use this function to determine a date a given number of working days in the future or past from a specified date.
You can also specify both which days are to be considered weekend (non-working) days and the holidays to be excluded. This function us useful for calculating start dates, delivery dates, and completion dates that need to factor in both working and non-working days.
語法
WORKDAY(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. |
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 will treat only Saturdays and Sundays as non-working days. |
範例
範例資料
| A (start_date) |
B (days) |
C (holidays) |
WORKDAY result |
|---|---|---|---|
| 1/1/2024 | 14 | 1/19/2024 | |
| 1/1/2024 | 14 | 1/11/2024 | 1/22/2024 |
| 1/1/2024 | -14 | 12/12/2023 | |
| 7/19/2023 | 9 | 12/31/2023 | 8/01/2023 |
| 45283 | 12 | 1/8/2024 | 01/08/2014 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=WORKDAY("1-Jul-2024",5) |
Determines the date that is the number of workdays (Mon-Fri) 5 days after 1 July 2024, with no holidays specified. | 8 July 2024 |
=WORKDAY(A1,B1) |
Determines the date that is the number of workdays (Mon-Fri) specified in cell B1 after the date in cell A1, with no holidays specified. | 1/9/2024 |
=WORKDAY(A1,-14) |
Determines the date that is the number of workdays (Mon-Fri) specified in cell B1 before the date in cell A1, with no holidays specified. | 12/12/2023 |
=WORKDAY(A2,B2,C2) |
Determines the date that is the number of workdays (Mon-Fri) between the date in cell A1 and the cell B1, with the holidays specified in cell C2. | 1/22/2024 |
=WORKDAY(A1,B1,C1:C5) |
Determines the number of workdays (Mon-Fri) between the date in cell A1 and the cell B1, with the holidays specified in the range E1:E5. | 1/10/2024 |
附註
- WORKDAY
- doesn't implicitly know which days are holidays. These have to be specified by the user.
- includes the start date when calculating workdays.
- automatically excludes all Saturdays and Sundays.
- ignores any time values.
- If the holiday date(s) are not between the specified start and end dates, they're ignored.
- If any of the provided date(s) are not valid, WORKDAY returns the #VALUE! 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