Yes, =WORKDAY(start_date, days, [holidays]) returns a date that’s X business days from a start date. But if you’re feeding it a holiday list without checking for weekend alignment or using relative references carelessly, you’ll silently deliver incorrect deadlines to your team.
The Setup
You’re managing vendor onboarding for Alibaba Cloud’s APAC partner program. Each new partner needs a 10-business-day implementation window after their signed agreement date. You also need to account for regional public holidays — some partners operate only in Singapore, others in Germany, so holidays aren’t uniform. Your raw data lives in A1:E10:
| Partner | Agreement Date | Region | Implementation Days | Holiday List Range |
|---|---|---|---|---|
| TechNova SG | 2024-04-01 | Singapore | 10 | Holidays_SG |
| NordicSoft A/S | 2024-04-05 | Germany | 10 | Holidays_DE |
| CloudBridge India | 2024-04-10 | India | 10 | Holidays_IN |
| AlphaCore JP | 2024-04-12 | Japan | 10 | Holidays_JP |
| DataSphere AU | 2024-04-15 | Australia | 10 | Holidays_AU |
| VistaLabs KR | 2024-04-18 | South Korea | 10 | Holidays_KR |
| ZenithFlow CA | 2024-04-22 | Canada | 10 | Holidays_CA |
| EdgeLogic MX | 2024-04-25 | Mexico | 10 | Holidays_MX |
Each holiday list is a named range: Holidays_SG contains 12 dates (e.g., 2024-05-01, 2024-05-20), Holidays_DE has 10, and so on — all stored on a separate sheet called Holidays. The goal? Calculate the exact go-live date for each partner — no weekends, no local holidays.
The Challenge
It’s not just about typing =WORKDAY(B2,D2,E2) and dragging down. Three things make this messy:
- You can’t reference
E2directly — Excel won’t resolve"Holidays_SG"as a range name unless you useINDIRECT(), andINDIRECT()breaks when you copy formulas across rows if the named ranges aren’t structured consistently. - If a holiday falls on a Saturday or Sunday,
WORKDAYignores it — which is correct behavior, but many users assume holidays override weekends. They don’t. That means your holiday list must contain only weekday dates, or you’ll accidentally double-count exclusion logic. - The biggest trap?
WORKDAYtreats negative day counts (-5) as “5 business days before”, but if your start date is already a holiday or weekend, it doesn’t “back up” past those — it lands on the first valid business day *before*, then counts backward. That trips up deadline recovery planning.
What makes this elegant is how cleanly it solves cross-regional scheduling — once set up right, one formula handles 50+ partners across 12 countries. But getting there requires precision, not guesswork.
Walking Through It
Let’s fix row 2: TechNova SG, Agreement Date 2024-04-01, 10 days, holidays from Holidays_SG.
Step 1 — Test the base calculation (no holidays)
Enter =WORKDAY(B2,D2) in F2. Result: 2024-04-15. That’s 10 weekdays later — correct. Monday April 1 → Friday April 12 is 10 days, so April 15 is the next Monday. Good.
| Partner | Agreement Date | Days | WORKDAY (no holidays) |
|---|---|---|---|
| TechNova SG | 2024-04-01 | 10 | 2024-04-15 |
Step 2 — Add holidays using INDIRECT
Type =WORKDAY(B2,D2,INDIRECT(E2)) in G2. Now it pulls from Holidays_SG. One holiday in that range falls between April 1–15: 2024-04-04 (Singapore’s National Day). So result becomes 2024-04-16 — skipping the 4th pushes the finish to Tuesday.
Step 3 — Lock references for safe copying
Change to =WORKDAY($B2,$D2,INDIRECT($E2)). Absolute column refs on B/D/E prevent misalignment when dragging down. Don’t forget: INDIRECT is volatile — but for 200 rows, it’s negligible. For >5k rows, switch to Power Query.
Surprising tip: If you want to exclude *both* weekends AND specific half-days (e.g., Dec 24 PM), WORKDAY can’t do it alone. But you *can* simulate it: add 0.5 to the day count, then wrap in INT() and adjust manually. Not built-in — but possible.
The Result
Here’s the final output in column H (“Go-Live Date”) after applying =WORKDAY($B2,$D2,INDIRECT($E2)) down to row 9:
| Partner | Agreement Date | Region | Days | Go-Live Date |
|---|---|---|---|---|
| TechNova SG | 2024-04-01 | Singapore | 10 | 2024-04-16 |
| NordicSoft A/S | 2024-04-05 | Germany | 10 | 2024-04-19 |
| CloudBridge India | 2024-04-10 | India | 10 | 2024-04-25 |
| AlphaCore JP | 2024-04-12 | Japan | 10 | 2024-04-26 |
| DataSphere AU | 2024-04-15 | Australia | 10 | 2024-04-30 |
| VistaLabs KR | 2024-04-18 | South Korea | 10 | 2024-05-03 |
| ZenithFlow CA | 2024-04-22 | Canada | 10 | 2024-05-06 |
| EdgeLogic MX | 2024-04-25 | Mexico | 10 | 2024-05-09 |
What Could Go Wrong
Here’s what actually happens — not what the docs say, but what your colleagues will paste into Slack at 4:58 PM on Friday:
| Symptom | Cause | Fix |
|---|---|---|
| #REF! error in cell | Named range in column E (e.g., "Holidays_SG") doesn’t exist or is misspelled | Use Formulas > Name Manager (Alt+M, M) to verify spelling and scope. Names are case-insensitive but space-sensitive. |
| Result is same as start date | Holiday list contains weekend dates (e.g., Saturday 2024-04-06) — WORKDAY ignores them, so no adjustment occurs | Filter holiday lists to weekdays only: =FILTER(Holidays_SG,WEEKDAY(Holidays_SG,2)<6) |
| Formula returns #VALUE! when dragged down | Relative reference like INDIRECT(E2) becomes INDIRECT(E3), but E3 contains "Holidays_DE" — and that range isn’t defined on the current sheet | Define all holiday ranges globally (scope = Workbook), not worksheet-specific. Or use INDIRECT("'Holidays'!"&E2) if ranges live on a dedicated sheet. |
One last thing: If you’re auditing someone else’s file and see =WORKDAY(A1,5) returning 2024-03-15 when A1 is 2024-03-08, don’t assume it’s wrong — check if March 11–15 included a holiday. Excel won’t tell you. You have to.
