WORKDAY

What it does

WORKDAY(start_date, days, [holidays])

WORKDAY returns the date that lies a given number of working days before or after a start date. Weekends — fixed at Saturday and Sunday — and any dates listed in holidays are skipped and do not consume the count.

Operationally: step day by day away from start_date in the direction given by the sign of days, decrementing a counter each time the day stepped onto is a working day, and stop when the counter reaches zero. The result is that day's serial.

The subtlety that this phrasing makes visible, and that the documentation does not settle, is whether start_date itself can be the answer. Microsoft's page does not state whether the start date participates in the count. The behaviour readers rely on — and the one that makes WORKDAY the inverse of NETWORKDAYS — is that days = 0 returns start_date unchanged and that counting begins from the next day. This Handbook has not verified it, and it is the first probe listed below. It matters: the two conventions differ by one working day on every call, which is a schedule that misses its deadline.

Arguments

WORKDAY(start_date, days, [holidays]) — two required arguments and one optional.

start_date — "a date that represents the start date".

days — "the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date." Microsoft states the conversion rule explicitly: "If days is not an integer, it is truncated." Truncation toward zero, on the ordinary reading — so -1.9 becomes -1, not -2. That negative branch is worth testing, since languages disagree about it.

holidays — "an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays". A range or array argument in an otherwise scalar signature; see the call pipeline for how such positions are prepared.

The misunderstood argument is days, in two ways: readers pass a calendar day count and expect weekends to be skipped on top of it (they are not — days is already in working days), and readers assume days = 1 means "tomorrow if tomorrow is a workday" without checking whether the start day itself counts.

Try it

Quirks

Result and edge cases

Returns a Number: a whole date serial.

• days = 0 is the case that reveals the counting convention. Under the expected reading it returns start_date — even when start_date is a Saturday, which is the input that distinguishes "return the start date" from "snap to the nearest working day".
• A start_date that is itself a weekend or holiday is the other diagnostic input, and for the same reason.
• Negative days walks backwards; the same convention questions apply mirrored.
• Holidays outside the traversed span are irrelevant. Duplicates and weekend-falling holidays should contribute nothing, since the operation is exclusion — undocumented.
• Blank cells in a holidays range raise the Empty-versus-Missing question from the value universe, and serial 0 lies inside the representable range, so a blank read as a number is a date. Undocumented, and a genuine hazard because holiday ranges are usually over-sized.
• The result carries no time component.

Errors

As documented on the Microsoft page:

• #VALUE! "if any argument is not a valid date".
• #NUM! "if start_date plus days yields an invalid date" — that is, when the offset walks off either end of the representable serial range.

The generalized sibling WORKDAY.INTL documents a fuller table, including #NUM! for an out-of-range date in holidays and #VALUE! for an invalid weekend string. Whether WORKDAY also errors on an out-of-range holiday is not stated on its own page and is not asserted here.

How to implement

Notes for implementers

1. Do not step day by day. With a fixed Saturday/Sunday weekend the answer has a closed form: divide days by 5 to get whole weeks, handle the remainder against the start day's weekday position, then apply holidays iteratively — and note that applying holidays can push the result into a new week, so the holiday pass has to iterate to a fixed point rather than run once.
2. The holiday fixed-point is the real algorithm. Adding a holiday moves the target, which may land on another holiday. An implementation that adjusts once is wrong for consecutive holidays, which is exactly what Christmas and Easter are.
3. Truncate days toward zero, per the documented word.
4. Pin the days = 0 convention and keep it identical in WORKDAY.INTL.
5. Normalize holidays to a set, dropping weekend and out-of-span entries first.

Rust code

19 workday_fn "WORKDAY" (2, 3) [V "start_date", V "days", R "holidays"] |s, a, sv| {
    workday(s, a, sv, false)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• NETWORKDAYS — the inverse question: given two dates, how many working days. The pair should round-trip — NETWORKDAYS(a, WORKDAY(a, n)) ought to be a simple function of n — but the exact relation depends on both functions' inclusivity conventions, neither of which is documented. Determining the constant is a one-experiment job and would settle both conventions at once.
• WORKDAY.INTL — the generalization with a weekend argument. WORKDAY(a, n, h) should equal WORKDAY.INTL(a, n, 1, h); unverified here.
• EDATE — offsetting by months rather than working days, and documented with the same "if it is not an integer, it is truncated" rule.
• WEEKDAY — the underlying day-of-week notion.
• Confusable. WORKDAY(a, n) and a + n differ by however many weekends and holidays fall in between, which is precisely why the function exists.

Named variants

No named variants are recorded yet.