Challenge Scenario
In project management and software development sprint planning, calculating work durations based on calendar days is inaccurate because engineering teams only work on standard business days (Monday through Friday).
By using the NETWORKDAYS function, project managers can exclude weekend days automatically and assign priority alerts to sprint initiatives approaching urgent deadlines.
As an Operations Project Manager, you are auditing four active sprint projects:
- Business Working Days (Column D): Calculate the exact number of workdays between
Start Date and Due Date (excluding Saturdays and Sundays) using NETWORKDAYS(B2, C2).
- Project Priority (Column E): If Working Days is 7 days or fewer (
D2 <= 7), assign "URGENT"; otherwise assign "NORMAL".
- Total Workday Capacity (Cell D7): Calculate total team workdays across all projects using
SUM(D2:D5).
- Urgent Sprint Count (Cell E7): Count how many projects require urgent attention using
COUNTIF(E2:E5, "URGENT").
Your Sheet Layout
Understanding how the sprint scheduling sheet is organized:
- Sprint Roadmap (Columns A to C): Shows Project Name, Start Date, and Target Completion Due Date.
- Workday Calculations & Priority (Columns D & E): Calculates net business days (excluding weekends) and assigns
URGENT vs NORMAL status.
- Sprint Capacity Summary (Cell D7 & E7): Aggregates total sprint capacity (37 working days) and counts urgent initiatives (2 projects).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Project Name, Start Date, Due Date |
Project Roadmap |
Project calendar schedule dates |
Read-only timeline milestones. |
D2:D5 |
Working Days |
Calculation |
=NETWORKDAYS(B2, C2) |
Calculates workdays excluding Saturdays & Sundays. |
E2:E5 |
Priority |
Priority Alert |
=IF(D2 <= 7, "URGENT", "NORMAL") |
Flags urgent short-duration initiatives (<= 7 days). |
D7 |
TOTAL WORKING DAYS |
Sprint Summary |
=SUM(D2:D5) |
Calculates total team workday capacity load (37). |
E7 |
URGENT PROJECT COUNT |
Sprint Summary |
=COUNTIF(E2:E5, "URGENT") |
Counts initiatives flagged as urgent (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column D (D2:D5), calculate the number of working business days between Start Date and Due Date using NETWORKDAYS.
2
In Column E (E2:E5), assign Priority using IF: If Working Days <= 7, assign "URGENT", otherwise "NORMAL".
3
In cell D7, calculate total working days using SUM(D2:D5).
4
In cell E7, count total urgent projects using COUNTIF(E2:E5, "URGENT").
Solution & Formula Breakdown
Let's examine how business workday calculations and priority flags work step by step:
1. Calculating Net Workdays with NETWORKDAYS (D2:D5)
=NETWORKDAYS(B2, C2)
The NETWORKDAYS(start_date, end_date) counts all Monday-through-Friday days inclusive:
- CRM Migration (2026-09-01 to 2026-09-15): 11 business workdays (excluding 4 weekend days) → 11.
- Mobile App Redesign (2026-09-01 to 2026-09-08): 6 business workdays (excluding 2 weekend days) → 6.
- Security Audit (2026-09-01 to 2026-09-22): 16 business workdays (excluding 6 weekend days) → 16.
- Server Patch (2026-09-01 to 2026-09-04): 4 business workdays → 4.
2. Flagging Sprint Priority with IF (E2:E5)
=IF(D2 <= 7, "URGENT", "NORMAL")
If Working Days in D2 is 7 or fewer, output "URGENT"; otherwise output "NORMAL".
3. Sprint Capacity Summary Rollups (D7 & E7)
- Total Working Days (Cell D7):
=SUM(D2:D5) adds 11 + 6 + 16 + 4 = 37 workdays.
- Urgent Projects (Cell E7):
=COUNTIF(E2:E5, "URGENT") returns 2 projects.
Both start and end dates are counted inclusively in NETWORKDAYS. A task running from Monday to Friday evaluates to exactly 5 working days!