What You Will Learn
Three projects. Each has a start date and an end date. You need to measure the gap in three different ways: total calendar days, working days (skipping weekends), and a segmented breakdown into years, months, and remaining days.
Different questions need different date measures. A project timeline might be 75 calendar days but only 54 working days. The table below shows how each formula answers a different question.
| Column | Formula | What It Tells You |
|---|
| D — Total Days | =C2-B2 | Raw calendar gap including weekends. |
| E — Working Days | =NETWORKDAYS(B2,C2) | Weekdays only. Useful for staffing and planning. |
| F:G:H — Segmented | =DATEDIF(B2,C2,"Y") etc. | Years, months, and days. Easier to read in reports. |
Fill D2:D4 and E2:E4 first, then the DATEDIF columns for each project row.
Three date measures from the same start and end dates.
The summary:
- B7:
=COUNTA(A2:A4) — counts how many projects were measured.
- C7:
=AVERAGE(D2:D4) — the average project length in calendar days across all projects.
How DATEDIF Works
DATEDIF takes three arguments: start date, end date, and a unit code.
"Y" — complete years between the dates."YM" — months left after counting years."MD" — days left after counting months.
For example, March 1 to May 15 is 0 years, 2 months, and 14 days. DATEDIF with "Y" returns 0, "YM" returns 2, and "MD" returns 14.
Why NETWORKDAYS Matters
The Bridge Repair project takes 75 calendar days (March 1 to May 15) but only 54 working days. The difference matters when you are scheduling people instead of counting wall time. NETWORKDAYS automatically excludes Saturdays and Sundays — it does not count them as workable days.
If a DATEDIF formula returns an error, check the date order — the start date must come before the end date. Also note that DATEDIF does not appear in Excel's formula autocomplete (it is a legacy function), but it still works if typed manually.
The AVERAGE formula in C7 uses the calendar day column (D2:D4), not the working days column. That is intentional — the average should represent wall time, not variable work schedules. Calendar days give a consistent baseline across all projects regardless of how weekends fall.
Use =C2-B2 in D2:D4 for calendar days, NETWORKDAYS in E2:E4 for working days, and DATEDIF in F2:H4 for the segmented breakdown. Use COUNTA in B7 and AVERAGE in C7.