Get Earliest Start & Latest Finish Project Dates by Phase (MINIFS & MAXIFS)

Summarize a phase without hiding incomplete dates

The five-task workbook contains three Design tasks and two Build tasks. The summary spans the earliest start through the latest finish in the selected phase. It includes weekends, overlapping tasks and gaps; it is not total task effort, working days or a critical-path calculation.

Use actual spreadsheet date values. The example creates them with DATE rather than relying on locale-sensitive date-text parsing. The E-column helpers require numeric whole-day serials in the supported positive Excel date range and a finish no earlier than its start. A missing or invalid date in a selected task stops the summary rather than silently ignoring that task.

G2 escapes wildcard characters and prefixes an equality operator so phase labels are literal criteria. Matching is case-insensitive; spaces remain significant. H2 distinguishes an empty query, no matching tasks, invalid selected dates and a ready summary. Invalid dates in a different phase do not block the selected phase.

Earliest start date

J2 formats the minimum selected start as yyyy-mm-dd only after the validation status is Ready. TEXT returns display text; retain the raw MINIFS serial instead when feeding a date-based chart.

=IF(H2<>"Ready",H2,TEXT(MINIFS(C2:C6,B2:B6,G2),"yyyy-mm-dd"))

Latest finish date

J3 uses the same validation and literal phase criterion for the latest finish. If a selected task moves later, the endpoint updates.

=IF(H2<>"Ready",H2,TEXT(MAXIFS(D2:D6,B2:B6,G2),"yyyy-mm-dd"))

Inclusive calendar span

J4 subtracts the first start from the last finish and adds one. A one-day task spans one calendar day. Design runs April 2–18 for 17 days; Build runs April 10–29 for 20 days. An absent phase returns a message rather than an apparent zero-day completed phase.

=IF(H2<>"Ready",H2,MAXIFS(D2:D6,B2:B6,G2)-MINIFS(C2:C6,B2:B6,G2)+1)

Further reading

• Microsoft — MINIFS function
• Microsoft — COUNTIF criteria