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 plan | Priya | Mon 2 Nov 2026 | 5 | Fri 6 Nov 2026 |
| Order furniture | Daniel | Mon 9 Nov 2026 | 10 | Fri 20 Nov 2026 |
| Network cabling | Sam | Mon 16 Nov 2026 | 8 | Wed 25 Nov 2026 |
| Phone & Wi-Fi setup | Sam | Mon 30 Nov 2026 | 6 | Mon 7 Dec 2026 |
| Pack & label crates | Lena | Mon 14 Dec 2026 | 9 | Thu 24 Dec 2026 |
| Move & unpack | Priya | Tue 29 Dec 2026 | 4 | Mon 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.
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.
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:
- 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.
- Look for weekend end dates. With the
dddformat, a “Sat” or “Sun” in the End column means a task was calculated with plain addition instead of WORKDAY. - 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?
- 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. - 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
- 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.
- 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. - 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.
- 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. - 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+#.
- 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. - 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). - 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).
=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.

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-1ignores 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-C2orDAYSgives 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 shortcut | What it does | Example |
|---|---|---|
WORKDAY | Date a number of working days after a start | =WORKDAY(C2,D2-1,$B$10:$B$12) |
NETWORKDAYS | Working days between two dates, both included | =NETWORKDAYS(C2,E7,B10:B12) = 43 |
WORKDAY.INTL | WORKDAY with a custom weekend | =WORKDAY.INTL(C2,D2-1,7,Holidays) |
NETWORKDAYS.INTL | NETWORKDAYS with a custom weekend | Friday–Saturday weekend: code 7 |
DATE | Builds a date from year, month, day | =DATE(2026,11,2) |
TODAY | Today’s date, updates daily | =NETWORKDAYS(TODAY(),E7,Holidays) |
TEXT | Shows a date as text, e.g. the weekday | =TEXT(E2,"dddd") |
| F4 | Adds or cycles $ signs in a reference | $B$10:$B$12 |
| Ctrl+; | Types today’s date as a fixed value | Status date |
| Ctrl+1 | Format Cells dialog | Custom format ddd d mmm yyyy |
| Ctrl+T | Turns a range into a table | Formulas 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.
- Percent Complete and Status Flags with IF
- A Simple Gantt Chart with Formulas and Conditional Formatting
- Milestone Tracker: Days Late and Late Flags
- Resource Hours by Person: SUMIFS, Load and Over-Allocation
- Budget vs Actual by Phase: SUMIF, Variance and a Chart
- Change Request Log: Approved Cost, Revised Budget and Finish
- RAID Log: Counting Open Risks, Issues and High Priorities
- Action Items: Days Open and Who to Chase
- Earned Value Basics: PV, EV, AC, CPI and SPI
- Weekly Status Report: Planned vs Actual, RAG and a Line Chart
- Choosing a Mover: Total Quotes, Weighted Scores and the Best Value
- Purchase Orders vs Invoices: Matching, Over-Billing and Missing Invoices
- Internal Labour Cost: Hours × Rates by Role and Phase
- Seat Plan: Desks Needed per Team, Desks per Floor and Space Left
- Crate Planning: Crates per Team, Spares, Van Trips and Cost
- Move-Weekend Schedule: End Times, Crew Overlaps and the Buffer Before Closing
- Snag List After the Move: Days to Fix, Open Snags by Floor and the Fix Target
- Post-Move Survey: Responses, % Favourable, Average Score and Response Rate
- Final Project: Close-Out Dashboard – Budget, Milestones, Snags, Survey and RAG