Excel Gantt Chart Formula: Build a Timeline Bar

The core Excel Gantt chart formula is =AND(H$1>=$B2,H$1<=$C2). Apply it as a conditional-formatting rule to the timeline grid: it colors a cell when the date in its column falls between that task’s start date in column B and end date in column C.

What the Gantt chart formula does

A formula-based Gantt chart is usually a task table beside a calendar grid. Each row represents a task, each timeline column represents a date, and conditional formatting fills the cells that fall within each task’s date range. The formula does not create a separate chart object; it determines which cells in the grid receive a format such as a fill color.

In the example formula, H$1 points to the date heading for the current timeline column. The dollar sign fixes the heading row while allowing the column to change as the rule is applied across the grid. $B2 and $C2 keep the start and end date columns fixed while allowing the row number to change for each task. The two comparisons, combined by AND, are true only when the timeline date is on or after the start and on or before the end.

This inclusive version counts both the first and last dates as part of the task. It works for calendar-day timelines and can also be used with a grid that displays only working days, as long as the headings contain real Excel dates.

Set up the task table and timeline

Start with a simple layout in a blank worksheet. Keep the dates in the task table and the timeline headings as actual date values, not text that merely looks like a date. For example, enter dates using Excel’s date format and then apply a display format such as mmm d if you want compact headings.

Put task names in A2:A20.
Put each task’s start date in B2:B20 and its end date in C2:C20.
Put the first timeline date in H1, the next date in I1, and continue across the row.
Use H2:AZ20 as the blank grid where the bars will appear.

For a concrete example, suppose a task starts March 2, 2026, and ends March 6, 2026. The chart will color the five date columns from March 2 through March 6, inclusive. Make sure the timeline extends far enough to show the full project; otherwise, a correctly calculated bar can appear cut off at the edge of the grid.

If you want a duration column, with the start date in B2 and the inclusive end date in C2, put this in D2 and copy it down: =C2-B2+1. For the March 2–6 example, the result is 5 calendar days. The added 1 matters because subtracting dates alone counts the difference between them, not both endpoint dates.

Apply the conditional-formatting rule

In Excel for Microsoft 365 on Windows, select H2:AZ20, then choose Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format, enter the rule below, choose Format to set a fill color, and confirm the dialogs.

With date headings in H1:AZ1, start dates in B2:B20, end dates in C2:C20, and the selected grid beginning at H2, use: =AND(H$1>=$B2,H$1<=$C2). This rule returns TRUE for each task-date intersection inside the task’s range, so Excel applies the chosen format there. It returns FALSE outside the range, leaving those cells unchanged.

The first cell in the selected range matters. The formula is written for H2, the upper-left cell. If your grid starts in a different column or row, adjust H and the row references to match that first cell. For example, if the grid starts at G3 and the date headings are in row 2, the formula must refer to G$2 for the date and row 3 for the first task. Excel then adjusts the relative references throughout the selected range.

On Excel for Mac, the same general approach is available through Home > Conditional Formatting > New Rule, though the dialog presentation can differ by version. Excel for the web also supports conditional formatting, but some rule-editing options can differ from the desktop app. If a rule is difficult to edit in the browser, open the workbook in desktop Excel.

Understand the date and reference choices

The dollar signs are essential when one rule is meant to fill a rectangular range. In H$1, the column is relative and the row is fixed: moving right changes H to I, while moving down keeps the reference on row 1. In $B2, the column is fixed and the row is relative: moving across keeps the start date in column B, while moving down changes row 2 to row 3 and so on. The same logic applies to $C2.

Check that every task has a valid start and end date and that the end is not earlier than the start. If the timeline headings are stored as text, comparisons may not work as intended. A quick check is to change a heading’s number format: if it behaves like a date and can be displayed in another date style, it is likely a real date value. Also avoid merged cells in the timeline grid; they interfere with a regular row-and-column structure.

Some plans treat an end date as the first day after a task is complete. That convention is called an exclusive end date, and it requires a different comparison. With date headings in H1:AZ1, start dates in B2:B20, and exclusive end dates in C2:C20, use =AND(H$1>=$B2,H$1<$C2). The strict less-than sign excludes the end date itself. Do not mix inclusive and exclusive conventions in the same project without documenting which one each task uses.

Calendar days, working days, and durations

The conditional-formatting rule follows the dates displayed in the header. If the header contains every calendar day, bars include weekends. If it contains only weekdays, the bars skip weekends because no weekend columns exist. The formula itself does not decide which days count as workdays; the timeline layout and date convention do.

For an inclusive working-day duration, with a task’s start date in B2 and end date in C2, enter =NETWORKDAYS(B2,C2). For March 2–6, 2026, the result is 5 working days because that date range runs Monday through Friday. NETWORKDAYS excludes Saturdays and Sundays by default. If your organization observes holidays, the function can also take a holiday-date range as an optional third argument; keep those holiday entries as real dates.

A working-day duration number is not a replacement for the start and end dates in the basic Gantt rule. The chart still needs dates to determine where a task begins and ends. For a weekday-only display, create or enter weekday date headings and apply the same conditional-formatting logic. That keeps the displayed bar aligned to working days without drawing weekend columns.

Show progress, milestones, and task status

A basic Gantt bar shows the planned date span, not how much work is complete. A useful extension is to add a percent-complete column and use a second formatting rule to distinguish completed portions from the remaining plan. This requires choosing a clear visual convention and a progress date or status value; the date-range formula alone cannot infer actual completion.

Milestones are often represented as a single date rather than a span. With the milestone date in B2 and timeline headings in H1:AZ1, a rule for the timeline row can use =H$1=$B2. Apply it to the milestone’s row and choose a distinct format. The formula marks only the column whose date equals the milestone date. If the milestone date is outside the displayed timeline, no cell will be marked.

Dependencies also need separate information. A task’s start and end dates do not tell Excel which task must finish before another can begin. You can add predecessor IDs or notes to the table, but the simple bar rule will not automatically reschedule dependent tasks. For a small plan, manually updating dates may be sufficient; for a complex schedule with changing dependencies, project-planning software may be a better fit.

Common problems and how to fix them

No cells are colored: Confirm that the date headings and task dates are real dates, not text, and that the rule was applied to the intended grid.
Bars are shifted by a column: Check that the formula’s first date reference matches the first timeline column. A rule applied from H2 should begin with H$1.
Every row uses the same dates: Check that the row number in the start and end references is relative, as in $B2 and $C2, rather than fixed as $B$2 and $C$2.
The bar includes an unwanted final day: Confirm whether your project uses an inclusive or exclusive end date. Use a less-than comparison for an exclusive end.
The bar stops at the grid edge: Extend the timeline columns to cover the task’s end date. Conditional formatting cannot show dates that are not present in the grid.
Old colors remain after changing a rule: Review Home > Conditional Formatting > Manage Rules and check the Applies to range. Remove obsolete rules or correct their ranges.

When checking a rule, select a cell that should be colored and inspect the formula relative to that cell. A common mistake is writing a correct-looking formula for one cell but applying it to a range whose top-left cell is different. Another is using the wrong date row after inserting rows above the timeline. Correcting the range and the references together is usually more reliable than repeatedly changing the fill color.

When a formula-based Gantt chart is a good choice

This approach is useful for small or medium schedules that need a customizable view inside a workbook. The task list stays editable, the bar colors update when dates change, and ordinary worksheet formatting can make the result easy to print or share. It can also be adapted for phases, resource assignments, or status colors by adding columns and conditional-formatting rules.

There are trade-offs. A daily grid can become very wide for a long project, and a formula-based sheet does not automatically provide dependency management, workload leveling, or a dedicated project schedule model. It is also a visual grid rather than a native Excel bar chart. For a compact plan, that flexibility may be enough; for frequent replanning across many linked tasks, compare it with a project-management application or a purpose-built Excel template.

Frequently Asked Questions

What is the formula for a Gantt chart in Excel?
The common conditional-formatting rule is =AND(H$1>=$B2,H$1<=$C2) when timeline dates are in row 1, task start dates are in column B, and end dates are in column C. Apply it to the timeline grid, adjusting the references to fit your layout.

Is an Excel Gantt chart a real chart?
Not necessarily. This method uses worksheet cells and conditional formatting to display bars, so it is often called a Gantt chart even though it is not an inserted chart object. Its cell-based structure makes the task table and timeline easy to customize.

How do I make the Gantt chart skip weekends?
Use timeline headings that include weekdays only, rather than creating a column for every calendar day. Keep those headings as real dates; the same date-range conditional-formatting rule will then color the weekday columns that fall within each task’s span.

Why is my Gantt chart formula not working?
Check the rule’s Applies to range, the first cell used in the formula, and whether all date entries are genuine Excel dates. Also verify that the start and end columns match the formula and that the task end date is not earlier than its start date.

Can a Gantt chart formula show percentage complete?
Not by itself. The basic rule marks the planned date range. To show progress, add a completion value or actual dates and create a separate visual rule that distinguishes completed work from the remaining schedule.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.