For a date-grid Gantt chart in Excel, use the conditional-formatting formula =AND(H$1>=$B2,H$1<=$C2). With task start dates in column B, finish dates in column C, and timeline dates across row 1 beginning in H1, it shades each task cell whose date falls between the start and finish, inclusive.
The formula does not draw a chart object by itself: it tells Excel which cells in a worksheet grid to color. This approach is quick to build, easy to update when dates change, and useful for a compact schedule. If you specifically need a bar chart object, a stacked bar chart is another option, explained below.
How the Gantt bar formula works
In the formula, H$1 refers to the date at the top of the current timeline column. The dollar sign fixes the header row while allowing the column to change as Excel evaluates cells to the right. The references $B2 and $C2 keep the start and finish columns fixed but allow the task row to change. The AND function returns TRUE only when both date tests pass.
Excel then applies the fill format you choose to cells for which the formula returns TRUE. For example, if the header date is March 4, 2025, and a task starts March 3 and finishes March 6, that cell is shaded. March 2 and March 7 are not shaded. The end date is included, which is why the formula uses a less-than-or-equal comparison.
These comparisons work because Excel stores ordinary dates as serial numbers and can compare them chronologically. The cells still need to contain real Excel dates, not text that merely looks like a date. A date number format changes how a date appears; it does not change the underlying date value.
Set up the worksheet and conditional formatting
Start with a simple table: put task names in column A, start dates in B, and finish dates in C. Put real dates in the timeline headings across row 1, beginning in H1, and reserve the cells below those headings for the bars. For example, H2:AL20 can be the schedule area for 19 tasks and 31 timeline columns.
- Enter one task per row, with a valid start and finish date. Keep the finish date on or after the start date.
- Enter the first timeline date in H1. Fill the following headings with consecutive dates, or enter them individually. Format the headings as dates such as d-mmm so they remain readable.
- Select the schedule cells, such as H2:AL20. Do not include the header row in the selection.
- On Windows, choose Home > Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.
- Enter the formula shown above, choose Format, select a fill color, and confirm the rule.
The formula assumes that the selected range begins in H2. If your timeline begins in a different column, change H in the formula to that first column. If the date headings are on another row, change the 1 in H$1 to that row number. Keep the dollar signs in their current positions so the references shift correctly across columns and down task rows.
In Excel for Mac, the same conditional-formatting approach is available from Home > Conditional Formatting > New Rule; the dialog wording or layout can vary slightly by release. Excel for the web also supports conditional formatting, though some dialog controls differ from the Windows desktop version.
Worked example: shade a task across its dates
Suppose row 2 is the task Prepare launch materials, B2 contains March 3, 2025, and C2 contains March 6, 2025. The timeline begins at H1 with March 1 and advances one day per column. With the rule applied to H2:AL20, the cells under March 3, March 4, March 5, and March 6 in row 2 receive the selected fill. That is four shaded cells, because both endpoints count.
For this example, the displayed task bar spans four calendar days even though its duration might be described differently in a working-day plan. The timeline itself controls what you see: a daily timeline includes weekend columns, while a weekly timeline shows a broader period. Ensure the timeline covers every task start and finish; a valid rule cannot shade dates outside the selected grid.
If you want to calculate inclusive calendar duration in a helper column, put a duration heading in D1 and use this formula in D2. With B2 as March 3 and C2 as March 6, the result is 4 days: =C2-B2+1. This helper is optional for the conditional-formatting grid; the bar formula compares the dates directly.
Adjust the formula for working days and milestones
A basic date-grid formula shades weekends if they fall within a task’s date range. If you want weekend columns to stay blank while retaining the same start and finish dates, add a weekday test. With timeline dates in H1:AL1 and task start and finish dates in B2 and C2, apply this formula to the schedule range: =AND(H$1>=$B2,H$1<=$C2,WEEKDAY(H$1,2)<=5). The second argument of WEEKDAY makes Monday 1 and Sunday 7, so values 1 through 5 are weekdays.
This changes only the shading; it does not calculate a workday finish date or move a task’s dates. If a task should skip nonworking days in its schedule logic, that is a separate date-planning problem. You can also leave weekend columns visible but use a lighter fill or a different column format to distinguish them.
For a milestone, a one-day fill can be sufficient when its start and finish are the same date. To give it a distinct color, add another conditional-formatting rule. With milestone dates stored in B2 and the timeline across row 1, use =H$1=$B2 for the milestone row, select a different format, and make sure the rule applies to the intended cells. This marks the matching timeline date; it does not create a diamond-shaped symbol.
Date and layout details that prevent errors
Use consistent date values in the task table and timeline. If a cell contains text such as a date imported from another system, Excel may not compare it as expected. A quick check is to change the cell’s number format to General: a real date normally displays as a number. Convert imported text to dates before troubleshooting the conditional-formatting rule.
Check the reference pattern carefully. The header row should be locked, as in H$1, so the rule always reads the timeline date for the current column. The date columns should be locked, as in $B2 and $C2, so the rule always reads the task’s dates. Locking both row and column in every reference can make every cell point to the same task or date and produce repeated, misplaced bars.
Decide whether finish dates are inclusive. The recommended formula includes the finish date. Some project systems treat a finish date as the first instant after a task ends; if your imported data follows that convention, an inclusive rule can make the bar appear one day too long. Confirm the meaning of the source dates before changing the comparison.
Grid formula versus a native Excel bar chart
A conditional-formatting grid is best when you want dates aligned in worksheet cells, task details beside the bars, and quick visual editing. It does not create a separate chart object, and it is less suited to presenting a very long schedule with many hundreds of date columns. Filtering, printing, and scrolling are also affected by the worksheet layout.
A native Gantt-style chart can be made from a stacked bar chart. Typically, the data includes a start-date series and a duration series. The start-date series positions each task’s bar along the date axis; format that series with no fill so only the duration appears. Reverse the task category order if needed so the first task appears at the top. The exact date-axis formatting depends on the data and chart layout.
For a chart duration helper, assume B2 holds the start date and C2 the inclusive finish date; place the result in D2. A March 3 through March 6 task returns 4: =C2-B2+1. This is the same inclusive calendar-day count as above. A chart is useful for a presentation or a separate visual, while a cell grid is often simpler when users need to inspect and edit the underlying schedule.
Common problems and practical fixes
- No cells are colored: Check that the task dates and timeline headings are real dates, that the selected range includes the task cells, and that the formula’s first column and header row match the range’s top-left cell.
- Bars appear on the wrong rows: Verify the task row reference, such as $B2, and confirm that the rule applies starting on row 2. A rule applied starting on row 3 needs its relative row references aligned to that row.
- Every row shows the same bar: Check that the start and finish columns are fixed with dollar signs, but the row number is not. The intended references are $B2 and $C2, not $B$2 and $C$2.
- The bar is one day too long: Confirm whether the finish date should count as a visible task day. The standard formula includes it.
- The bar stops early: Extend the timeline headings and the conditional-formatting range. Adding date headings alone does not necessarily extend the rule’s Applies to range.
To inspect a rule in Microsoft 365 for Windows, select a cell in the schedule and choose Home > Conditional Formatting > Manage Rules. Check the Applies to range and the formula shown for the rule. If several formatting rules overlap, review their order and formats so another rule is not obscuring the bar color.
Frequently Asked Questions
What is the formula for a Gantt chart bar in Excel?
For a date grid with timeline dates in row 1 and task start and finish dates in columns B and C, use =AND(H$1>=$B2,H$1<=$C2) as a conditional-formatting rule. Set the fill color in the rule rather than adding a formula to each schedule cell.
Can Excel make a Gantt chart automatically?
Excel does not have a single Gantt Chart button in the standard worksheet workflow. You can create a cell-based Gantt view with conditional formatting, or use a stacked bar chart to make a separate chart object. In either case, task dates need to be organized consistently.
Why does my Gantt formula not work?
The most common causes are text-formatted dates, a selected range that starts in a different column or row than the formula assumes, and incorrect dollar signs in the references. Check the rule’s Applies to range and verify that the headings and task dates are actual Excel date values.
How do I show weekends in an Excel Gantt chart?
The basic formula shades every date between the task start and finish, including Saturday and Sunday. To leave weekends unshaded, add the WEEKDAY condition described above, or use separate formatting to visually distinguish weekend columns.
Can I use this formula for a project plan with a different start column?
Yes. Replace H with the first column of your timeline and adjust the header row reference if necessary. Keep the header row fixed and the start and finish columns fixed while leaving the task row relative, then apply the rule to the corresponding schedule range.
Related
- Excel Worksheet vs CSV: Key Differences and When to Use Each
- Excel Wrap Text Shortcut: Keys, Ribbon, and Fixes
- Excel: How to Work Out the Difference Between Two Numbers
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Chart Multiple Lines: How to Create ThemYes. To show multiple lines in an Excel PivotChart, create a PivotChart from your PivotTable, choose a line…
- Excel Pivot Table Hide Filter: What You Can RemoveTo hide Excel PivotTable filter arrows without removing the current filter, select the PivotTable and choose PivotTable Analyze…
- How to Create a Pie Chart in Google Sheets Step by StepTo create a pie chart in Google Sheets, arrange your categories in one column and their numeric values…
- Excel Pie Chart With Other Category: How to Group SlicesTo make an Excel pie chart with an Other category, select the data and choose Insert > Insert…
- Excel Dashboard Inspo: Ideas, Layouts, and Design TipsFor Excel dashboard inspo, start with one clear question your dashboard should answer, then build a compact view…
- Bar Chart in Cell Excel: How to Make OneExcel does not put a standard bar chart inside a worksheet cell, but you can create an in-cell…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.