Gantt chart formula: build a dynamic project timeline grid in cells

Mixed references ($B2, $C2, D$1) power an entire Gantt matrix

A spreadsheet Gantt grid places task Start and End dates in columns B and C and timeline dates across row 1 (D1, E1, F1...).

In cell D2, IF(AND(D$1 >= $B2, D$1 <= $C2), "X", "") locks row 1 on the timeline header (D$1) and columns B and C on the task window ($B2, $C2) so one formula drags across every column and task row.

Daily/weekly Gantt cell formula and project span rollup

For weekly headers where D$1 is Monday of the week, test AND($B2 <= D$1 + 6, $C2 >= D$1) so mid-week tasks still light up their week column.

=IF(AND(E$1 >= $B2, E$1 <= $C2), "X", "")

Overall project start, end, and total calendar span

MIN(B2:B3) finds project kickoff, MAX(C2:C3) finds final delivery, and MAX - MIN + 1 counts inclusive calendar days.

=MAX(C2:C3) - MIN(B2:B3) + 1

Inputs and interpretation

The sample headers are individual dates spaced seven days apart; the displayed formula marks those dates, not every day in each week. Use the interval-overlap variant for a full week bucket. Require populated numeric date-only values with End >= Start. The project-span rollup assumes at least one valid task and includes both endpoint days. An X grid is not a dependency/resource scheduling engine.

Further reading

• Microsoft Support — Present your data in a Gantt chart in Excel