Date & Time Formulas
Get Project Start Date, End Date & Total Span by Project (MINIFS & MAXIFS)
Roll up task-level schedules into overall project kickoff date MINIFS, final completion date MAXIFS, and inclusive calendar duration MAXIFS - MINIFS + 1.
Rolling up parallel workstream tasks into true master project boundaries!
In a multi-project Gantt log, summing individual task durations wildly overstates overall project calendar time whenever design, backend, and QA tasks overlap in parallel! Instead, master portfolio rollups extract the earliest task start date via MINIFS(Start_Range, Project_Range, Target_Project) and the latest task finish date via MAXIFS(End_Range, Project_Range, Target_Project)!
Watch out for MINIFS/MAXIFS’s silent zero trap: when a queried project code has zero matching tasks in the log, both MINIFS and MAXIFS return numeric 0—which Excel formats as the phantom date "1900-01-00"! Wrapping with COUNTIF(A2:A7, F2) = 0 prevents bogus 1900 dates and computes inclusive calendar span MAXIFS(...) - MINIFS(...) + 1 cleanly!
| Queried Project ID | Earliest Start (MINIFS) | Latest Finish (MAXIFS) | Inclusive Span (End - Start + 1) |
|---|---|---|---|
| PRJ-APOLLO (3 Overlapping Tasks) | 2026-04-06 | 2026-05-15 | 40 Calendar Days |
| PRJ-VEGA (2 Sequential Tasks) | 2026-04-20 | 2026-05-08 | 19 Calendar Days |
| PRJ-NONE (Unscheduled Project) | COUNTIF = 0 Guard | "No Tasks Found" | Prevents 1900-01-00 Serial Zero! |
Formatted ISO Project Start Date via MINIFS
Guards missing project codes with COUNTIF before formatting MINIFS(C2:C6, A2:A6, F2) as "yyyy-mm-dd".
=IF(COUNTIF(A2:A6, F2) = 0, "No Tasks Found", TEXT(MINIFS(C2:C6, A2:A6, F2), "yyyy-mm-dd"))Inclusive Project Calendar Span Days (MAXIFS - MINIFS + 1)
Subtracts earliest task kickoff from latest task finish and adds 1 so a single-day milestone counts as 1 day.
=IF(COUNTIF(A2:A6, F2) = 0, "No Tasks Found", MAXIFS(D2:D6, A2:A6, F2) - MINIFS(C2:C6, A2:A6, F2) + 1)Try the example
Use the original values or change them to test your understanding.