Knowledge base / Excel

WORKDAY in Excel: deadlines, weekends and holidays

I count five working days after a Monday, then add a day off. The examples also cover weekend starts and moving backwards.

Kajo RudzińskiPublished Updated 5 min read

A path across calendar tiles skips two dark columns and reaches a crate

I have an order date and five working days before the next stage is due. Adding five to the date moves five calendar days, including Saturday and Sunday. When the deadline should skip weekends, I use WORKDAY.

The function returns a date after a specified working-day offset. This entry uses small synthetic examples. The company calendar is an assumption of the example; the holiday list is not a public holiday calendar for any particular country.

Syntax and arguments

In English Excel, I write:

=WORKDAY(start_date,days,[holidays])

Square brackets in the syntax description indicate an optional argument. I do not type them into the actual formula. start_date is the reference date, days is the offset, and holidays is a range of additional dates to exclude. The ordinary function skips Saturdays and Sundays.

A positive offset gives a later date, while a negative offset gives an earlier date. Microsoft describes that behaviour in the WORKDAY documentation. The start is a reference point: an offset of one working day moves to the next working day.

English examples here use commas. Argument separators depend on regional settings. The Polish function names are DZIEŃ.ROBOCZY and DATA for WORKDAY and DATE. Microsoft documents WORKDAY for Microsoft 365 and versions including Excel 2024, 2021, 2019 and 2016.

Five days after a Monday

Take Monday, 3 June 2024. With no additional holidays, I enter:

=WORKDAY(DATE(2024,6,3),5)

The result is 10 June 2024, the following Monday. I can follow the calendar one step at a time:

Counted working day Date Weekday
1 2024-06-04 Tuesday
2 2024-06-05 Wednesday
3 2024-06-06 Thursday
4 2024-06-07 Friday
5 2024-06-10 Monday

Saturday and Sunday do not consume the offset. The start date, 3 June, is also not the first day of that offset. If your process calls the starting date “day one,” agree that counting convention separately. The formula should reflect the rule the process actually uses.

I construct the date with DATE to avoid ambiguous text such as 03/06/2024. In a finished workbook, I can reference a cell containing a real Excel date. The result stores a date as a number too, so I format the result cell as a date.

An extra day off moves the deadline

In D2, I enter =DATE(2024,6,10). In our synthetic calendar, this is an additional company closure. I then use:

=WORKDAY(DATE(2024,6,3),5,D2:D2)

Tuesday through Friday remain the first four counted days. Saturday and Sunday are excluded as weekends, and Monday, 10 June, is excluded by the list. The fifth working day becomes Tuesday, 11 June.

The function does not automatically retrieve a country’s public holidays. I maintain the list. For multiple orders, I keep it in one place and describe the location and period it covers. If teams use different calendars, I avoid assuming one common range will fit them all.

When copying the formula down an order list, I fix the holiday range if it should stay shared, for example $D$2:$D$10. Order dates may change by row, while an accidentally shifting holiday range would change the deadline rules.

Two deadline paths: an extra day off forces a detour on the lower track

Weekend starts and negative offsets

Starting on a Saturday does not force a Saturday result. For Saturday, 1 June 2024, with a positive offset of five working days, Monday, 3 June, is the first counted day and Friday, 7 June, is the fifth.

Start Offset Additional days off Result
2024-06-01 5 none 2024-06-07
2024-06-01 10 none 2024-06-14
2024-06-01 5 2024-06-05 2024-06-10
2024-06-03 -1 none 2024-05-31

In the last row, I move back from Monday to the previous Friday. A negative day count is a valid way to calculate an earlier date. There is no need to adjust that result manually for the weekend.

Different weekends with WORKDAY.INTL

When Friday and Saturday are days off, I use WORKDAY.INTL. Its Polish name is DZIEŃ.ROBOCZY.NIESTAND.

=WORKDAY.INTL(DATE(2024,6,3),5,7)

The argument 7 specifies a Friday-Saturday weekend. According to Microsoft’s weekend code table, 16 means Friday only, while 5 means Wednesday and Thursday. These codes are not ordinary weekday numbers.

For this example, the counted dates are Tuesday the 4th, Wednesday the 5th, Thursday the 6th, Sunday the 9th and Monday the 10th of June. The result is 10 June. An additional holiday list belongs in the fourth argument of this function.

Limits of the calculation

WORKDAY counts whole days. Its documentation specifies that a fractional day offset is truncated. An offset of 1.9 therefore does not create a schedule ending after almost two working days. Hourly deadlines, shifts and opening hours need a more detailed model.

Invalid dates can produce a value error; a result outside the supported date range can produce a numeric error. I inspect the inputs and their data types first. Changing a text cell’s display format to a date does not guarantee that the text has been converted into a real date.

A calendar-based deadline is not a delivery confirmation or a record of employee availability. Lead time, the starting rule and closure dates remain process assumptions. I state the calendar used in the report.

If you need a count of days within an interval rather than a resulting date, see NETWORKDAYS. For an exercise, move the extra day off from Monday, 10 June, to Tuesday, 4 June, and list the five counted dates. Work through the calendar first, then compare it with your workbook.

Key takeaways

Does the starting date count as the first day of the offset?

A positive offset counts subsequent working days after the start. One day means the next working day.

Does Excel automatically retrieve public holidays?

No. Supply the dates to exclude in the holidays argument and maintain the appropriate calendar.

How do I make Friday and Saturday the weekend?

Use WORKDAY.INTL with weekend code 7. Code 16 means Friday only.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.