Tabellab

Task List: End Dates with WORKDAY and Holidays

Lesson 1 of 20Lab

All lessons

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Read first

In Excel

  • Formulas → Date & Time → WORKDAY. Format the result as a date if Excel shows a number such as 46351.
  • WORKDAY.INTL lets you choose other weekends, e.g. Friday–Saturday.
Loading the spreadsheet…

Why end dates are where projects go wrong

Ask any project coordinator what gets them in trouble and dates come up quickly. A task “takes ten days”, someone adds ten to the start date, and the plan promises a finish on a Sunday. Or the plan forgets a public holiday, the supplier is closed, and every later task slips by a day without anyone noticing until the steering group asks why the move date changed. The task list is the backbone of a project, and its end dates are the promises the team will be held to.

This lesson is for project coordinators, PMO assistants, office managers who suddenly own a project, team leads who plan work in Excel, and anyone preparing for a first role on a project team. You do not need project management software. Excel’s WORKDAY and NETWORKDAYS functions handle weekends and holidays correctly, and once you understand one small detail, the “minus one”, your dates will match what the team actually experiences.

We follow Priya Nair, project coordinator for the head-office move of Ashby & Lane Insurance, a fictional company. The move has six tasks between November 2026 and early January 2027, and three US public holidays fall inside that window. All names and dates in the plan are invented for teaching.

The core concept: working days, not calendar days

A minimal task list has five columns: the task, an owner, a start date, a duration in working days and an end date. Durations are estimated in working days because that is how people work: “five days of effort” means Monday to Friday, not Monday to the following Friday-minus-two-weekend-days.

Excel stores dates as serial numbers, which is why you can add and subtract them. But =C2+D2 counts every calendar day, including Saturdays, Sundays and holidays. Excel has two functions that understand the working week:

  • WORKDAY(start_date, days, [holidays]) returns the date that is a given number of working days after the start date. It skips Saturdays, Sundays and every date in the optional holiday range.
  • NETWORKDAYS(start_date, end_date, [holidays]) does the reverse: it counts the working days between two dates, and it counts both the start and the end date.
  • WORKDAY.INTL and NETWORKDAYS.INTL do the same with a different weekend, for example Friday and Saturday, using a weekend code such as 7, or a seven-character string like "0000011".

Now the detail that catches almost everyone. WORKDAY counts days after the start. =WORKDAY(Monday,5) returns the following Monday, because it moves five working days forward from Monday. But a five-day task that starts on Monday is worked on Monday, Tuesday, Wednesday, Thursday and Friday, so it ends on Friday. The start day is day 1. That is why the end-date formula subtracts one from the duration: =WORKDAY(C2,D2-1,$B$10:$B$12). Leave out the -1 and every task in the plan ends one working day late.

The holiday list sits in its own small range, B10:B12 in the lab, and is locked with dollar signs so that every row of the task list points to the same cells. That design has two benefits: one list feeds every formula, and next year you only change three dates instead of editing dozens of formulas.

Worked example: the Ashby & Lane office move

Here is Priya’s finished task list. The start dates and durations are inputs; column E is the formula. The holiday list is Thanksgiving (Thu 26 Nov 2026), Christmas Day (Fri 25 Dec 2026) and New Year’s Day (Fri 1 Jan 2027).

Task (A)Owner (B)Start (C)Days (D)End (E)
Confirm floor planPriyaMon 2 Nov 20265Fri 6 Nov 2026
Order furnitureDanielMon 9 Nov 202610Fri 20 Nov 2026
Network cablingSamMon 16 Nov 20268Wed 25 Nov 2026
Phone & Wi-Fi setupSamMon 30 Nov 20266Mon 7 Dec 2026
Pack & label cratesLenaMon 14 Dec 20269Thu 24 Dec 2026
Move & unpackPriyaTue 29 Dec 20264Mon 4 Jan 2027

Every end date comes from the same formula, copied down: =WORKDAY(C2,D2-1,$B$10:$B$12) in E2, which becomes =WORKDAY(C7,D7-1,$B$10:$B$12) in E7. Check one by hand to build trust in it. Confirm floor plan starts on Monday 2 November and takes 5 days, so it ends on Friday 6 November. Order furniture starts on Monday 9 November and takes 10 days, two full working weeks, so it ends on Friday 20 November.

The interesting row is the last one. Move & unpack starts on Tuesday 29 December and takes 4 working days. Tuesday, Wednesday and Thursday are days 1 to 3. Friday 1 January is New Year’s Day, and the weekend follows, so day 4 is Monday 4 January 2027. Without the holiday list, Excel would return Friday 1 January, a day the office is closed. Notice also how close Network cabling comes to Thanksgiving: it ends on Wednesday 25 November, the day before the holiday. If its duration grew by a single day, WORKDAY would skip Thanksgiving and the end date would land on Friday 27 November, two days later instead of one, leaving only the weekend before Phone & Wi-Fi setup starts on Monday 30 November. A good coordinator flags tasks that sit right next to a holiday.

Office move: tasks with WORKDAY end dates2 Nov9 Nov16 Nov23 Nov30 Nov7 Dec14 Dec21 Dec28 Dec4 JanConfirm floor planOrder furnitureNetwork cablingPhone & Wi-Fi setupPack & label cratesMove & unpackThanksgivingChristmasNew YearDashed = holiday
Figure 1. The six tasks of the Ashby & Lane office move as bars from start to WORKDAY end date. Week lines are Mondays. Only the last task, Move & unpack, runs across a holiday (New Year’s Day), so it is the only one whose end date moves.

The final cell of the lab counts the working days of the whole move, from the first start to the last end: =NETWORKDAYS(C2,E7,B10:B12). The answer is 43 working days between Monday 2 November 2026 and Monday 4 January 2027, with both ends included and the three holidays left out. That single number is what you quote when someone asks how long the project takes in team time, as opposed to calendar weeks.

How =WORKDAY(C7,D7-1,$B$10:$B$12) counts 4 working daysTue29 DecDay 1 (start)Wed30 DecDay 2Thu31 DecDay 3Fri1 JanHolidaySat2 JanWeekendSun3 JanWeekendMon4 JanDay 4 (end)Start counts as day 1, so Excel adds D7−1 = 3 working days after Tue 29 Dec.Without the holiday list the result would be Fri 1 Jan, a day the office is closed.With =WORKDAY(C7,D7,…) (no −1) it would be Tue 5 Jan, one working day too late.
Figure 2. WORKDAY skips the weekend and every date in the holiday list. The start day is day 1, which is why the formula adds the duration minus one.

A five-minute check before you share the plan

Dates are the part of a plan that other people copy into their own calendars, so it pays to check them before the file leaves your desk. A short routine catches almost every error:

  1. Test one row by hand. Pick the simplest task, count the days on a calendar and compare with Excel. If they differ, the formula is wrong for every row.
  2. Look for weekend end dates. With the ddd format, a “Sat” or “Sun” in the End column means a task was calculated with plain addition instead of WORKDAY.
  3. Check the holiday list. Are all dates real dates, in the right year, for the right country? Does the list cover the whole project period?
  4. Compare durations back. In a helper column, =NETWORKDAYS(C2,E2,Holidays) must return exactly the duration in column D. Any mismatch points to a wrong row.
  5. Scan for overlaps and gaps. Where one task must finish before the next one starts, the next start date should be after the previous end date.

The fourth check is the most powerful one, because NETWORKDAYS is the exact reverse of the end-date formula. For Network cabling, =NETWORKDAYS(C4,E4,$B$10:$B$12) returns 8, the duration in D4. If you copy that check down and every row matches, your end dates are right.

How to build the task list in Excel, step by step

  1. In a new sheet, type the headers in row 1: Task, Owner, Start, Days, End. Make them bold with Ctrl+B and freeze the row with View → Freeze Panes → Freeze Top Row.
  2. Enter each task, its owner and its duration in working days. Type start dates as real dates, for example 2/11/2026 or 11/2/2026 depending on your regional settings, or use =DATE(2026,11,2), which works in every region.
  3. Below the task list, type a small table titled Holidays with one date per row. Use your organisation’s official holiday calendar for the country where the work happens.
  4. Click E2 and choose Formulas → Date & Time → WORKDAY, or simply type =WORKDAY(C2,D2-1,$B$10:$B$12). Press F4 while the cursor is in the holiday reference to add the dollar signs.
  5. Copy the formula down by double-clicking the fill handle. If the cells show numbers such as 46332, format them as dates: Home → Number → Short Date, or Ctrl+Shift+#.
  6. To show the weekday next to each date, use a custom format: Ctrl+1 → Number → Custom → ddd d mmm yyyy. A Saturday or Sunday in the End column is then impossible to miss.
  7. Below the list, count the project’s working days with =NETWORKDAYS(C2,E7,B10:B12), or for a longer list =NETWORKDAYS(MIN(C2:C7),MAX(E2:E7),B10:B12).
  8. Turn the range into a table with Ctrl+T so new tasks pick up the formula automatically, and consider naming the holiday range (Formulas → Define Name, e.g. Holidays) so the formula reads =WORKDAY(C2,D2-1,Holidays).
If a task follows another one, link its start to the predecessor instead of typing a date: =WORKDAY(E2,1,Holidays) gives the next working day after E2. Now a delay in one task moves every following task automatically.

Different weekends with WORKDAY.INTL

Not every team works Monday to Friday. A site team may work Monday to Saturday, and in several countries the weekend is Friday and Saturday. WORKDAY.INTL takes a weekend argument between the duration and the holidays. Weekend code 1 is Saturday–Sunday (the default), 7 is Friday–Saturday and 11 is Sunday only. You can also pass a seven-character string from Monday to Sunday, where 1 means a day off: =WORKDAY.INTL(C2,D2-1,"0000011",Holidays) is the same as a normal WORKDAY, while "0000001" means only Sunday is off. Use the same weekend setting in NETWORKDAYS.INTL so the two functions agree.

Project coordinator planning an office move task list with dates in Excel
A task list with real working-day end dates is the first sheet of almost every project file. The lab above builds it for the Ashby & Lane move.

Common mistakes and how to spot them

  • Forgetting the minus one. If a five-day task that starts on Monday ends on the next Monday, the formula uses D2 instead of D2-1. Check one simple row by hand.
  • Adding calendar days. =C2+D2-1 ignores weekends. A custom date format with the weekday (ddd) shows Saturdays and Sundays in the End column straight away.
  • Missing or unlocked holidays. Without the third argument, holidays are counted as working days. Without the dollar signs, the range shifts as you copy the formula down and later rows look at empty cells.
  • Holidays stored as text. If a holiday is typed as text, for example with an apostrophe in front, WORKDAY ignores it or returns #VALUE!. Real dates align to the right of the cell by default; text aligns to the left.
  • Numbers instead of dates. A result like 46351 is a correct date in the wrong format. Apply a date format, do not retype it.
  • Mixing up NETWORKDAYS and DAYS. =E7-C2 or DAYS gives calendar days. NETWORKDAYS gives working days and includes both the start and the end date.
  • Using a holiday calendar from the wrong country. A team in another office may have different public holidays. Keep one holiday list per location if the project spans several.

Quick reference: functions and shortcuts used

These are the date tools a project coordinator uses every week.

Function or shortcutWhat it doesExample
WORKDAYDate a number of working days after a start=WORKDAY(C2,D2-1,$B$10:$B$12)
NETWORKDAYSWorking days between two dates, both included=NETWORKDAYS(C2,E7,B10:B12) = 43
WORKDAY.INTLWORKDAY with a custom weekend=WORKDAY.INTL(C2,D2-1,7,Holidays)
NETWORKDAYS.INTLNETWORKDAYS with a custom weekendFriday–Saturday weekend: code 7
DATEBuilds a date from year, month, day=DATE(2026,11,2)
TODAYToday’s date, updates daily=NETWORKDAYS(TODAY(),E7,Holidays)
TEXTShows a date as text, e.g. the weekday=TEXT(E2,"dddd")
F4Adds or cycles $ signs in a reference$B$10:$B$12
Ctrl+;Types today’s date as a fixed valueStatus date
Ctrl+1Format Cells dialogCustom format ddd d mmm yyyy
Ctrl+TTurns a range into a tableFormulas fill new rows automatically

Real-world uses

  • Office moves and fit-outs. Exactly the plan in this lesson: suppliers, cabling, IT setup and the move itself, each with a firm end date that respects holidays.
  • Remaining working days. =NETWORKDAYS(TODAY(),E7,Holidays) tells the team how many working days are left before a deadline, a number that updates itself every morning.
  • Supplier lead times. If a supplier quotes “15 working days from order”, =WORKDAY(order_date,15,Holidays) gives the delivery date to put in the plan. Check whether the supplier counts the order day itself and use the minus one only if it does.
  • Contract and approval deadlines. Many processes are measured in business days, for example a review period of ten working days. WORKDAY turns those rules into dates you can track.
  • International teams. With WORKDAY.INTL and one holiday list per office, you can plan the same task for teams with different weekends and public holidays.

What you will build in the rest of this course

This first lab is free. The other nineteen labs are part of the paid course ($9.90, one-time) and continue with the same office move, so the task list you build here grows into a full project file.

Percent Complete and Status Flags with IF. You turn hours planned and hours done into a % complete for every task and let Excel flag each one as Done, Overdue or On track.

A Simple Gantt Chart with Formulas and Conditional Formatting. You build a week-by-week Gantt chart in the grid: a formula marks the weeks each task runs and a colour rule turns the marks into bars.

Milestone Tracker: Days Late and Late Flags. You compare each milestone’s forecast or actual date with the baseline, show how many days it is late and count the late milestones.

Resource Hours by Person: SUMIFS, Load and Over-Allocation. You add up the hours each team member is booked in a week, compare them with capacity and flag anyone who is over-allocated.

Budget vs Actual by Phase: SUMIF, Variance and a Chart. You total supplier invoices by phase, compare them with the budget and chart budget against actual for the steering group.

Change Request Log: Approved Cost, Revised Budget and Finish. You total change requests by status and work out the revised budget and the revised finish date.

RAID Log: Counting Open Risks, Issues and High Priorities. You summarise the RAID log for the weekly meeting: open items per type, the open high-priority ones and the share already closed.

Action Items: Days Open and Who to Chase. You age the actions from the weekly project meeting, find the ones to chase and count how many are still open.

Earned Value Basics: PV, EV, AC, CPI and SPI. You calculate planned value and earned value per phase and the two indices that tell a project manager whether the work is on budget and on schedule.

Weekly Status Report: Planned vs Actual, RAG and a Line Chart. You prepare the weekly status report: the gap between planned and actual progress, a RAG status for each week and a progress chart.

Summary

Plan task durations in working days and let Excel turn them into dates. =WORKDAY(C2,D2-1,$B$10:$B$12) gives the end date of a task whose start day counts as day 1, skipping weekends and every date in a locked holiday list. =NETWORKDAYS(C2,E7,B10:B12) counts the working days of the whole project, 43 for the Ashby & Lane move. Check one row by hand, show the weekday in the date format, and watch the tasks that sit right next to a holiday.

Now scroll up to the lab and build Priya’s task list yourself. It takes about fifteen minutes, every formula is checked as you type, and the finished sheet is a template you can reuse for your next project.

Frequently asked questions

How do I calculate an end date excluding weekends in Excel?

Use WORKDAY: =WORKDAY(start,days-1,holidays) if the start day counts as day 1. WORKDAY skips Saturdays, Sundays and any dates in the holiday range.

Why does my WORKDAY end date come out one day late?

WORKDAY counts working days after the start date. If the task is also worked on its start day, subtract one from the duration, for example =WORKDAY(C2,D2-1,Holidays).

How do I add holidays to WORKDAY?

List the holiday dates in a range and pass it as the third argument, locked with dollar signs, for example =WORKDAY(C2,D2-1,$B$10:$B$12). The dates must be real Excel dates, not text.

What is the difference between WORKDAY and NETWORKDAYS?

WORKDAY returns a date a number of working days from a start date. NETWORKDAYS counts the working days between two dates and includes both the start and the end date.

How do I use a Friday–Saturday weekend?

Use WORKDAY.INTL or NETWORKDAYS.INTL with weekend code 7, for example =WORKDAY.INTL(C2,D2-1,7,Holidays). A seven-character string such as "0000110" also works.

Why does WORKDAY return a number like 46351?

Excel stores dates as serial numbers. The result is correct; format the cell as a date with Home, Number, Short Date or Ctrl+Shift+#.

Keep going: Excel for Project Coordinators: Task Lists, Timelines, Budgets & Status Reports

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Percent Complete and Status Flags with IF
  2. A Simple Gantt Chart with Formulas and Conditional Formatting
  3. Milestone Tracker: Days Late and Late Flags
  4. Resource Hours by Person: SUMIFS, Load and Over-Allocation
  5. Budget vs Actual by Phase: SUMIF, Variance and a Chart
  6. Change Request Log: Approved Cost, Revised Budget and Finish
  7. RAID Log: Counting Open Risks, Issues and High Priorities
  8. Action Items: Days Open and Who to Chase
  9. Earned Value Basics: PV, EV, AC, CPI and SPI
  10. Weekly Status Report: Planned vs Actual, RAG and a Line Chart
  11. Choosing a Mover: Total Quotes, Weighted Scores and the Best Value
  12. Purchase Orders vs Invoices: Matching, Over-Billing and Missing Invoices
  13. Internal Labour Cost: Hours × Rates by Role and Phase
  14. Seat Plan: Desks Needed per Team, Desks per Floor and Space Left
  15. Crate Planning: Crates per Team, Spares, Van Trips and Cost
  16. Move-Weekend Schedule: End Times, Crew Overlaps and the Buffer Before Closing
  17. Snag List After the Move: Days to Fix, Open Snags by Floor and the Fix Target
  18. Post-Move Survey: Responses, % Favourable, Average Score and Response Rate
  19. Final Project: Close-Out Dashboard – Budget, Milestones, Snags, Survey and RAG

Buy course — $9.90 See the full course