To show a Gantt bar for dates between a task’s start and finish in Excel, use conditional formatting with =AND(D$1>=$B2,D$1<=$C2). With task start dates in column B, finish dates in column C, and timeline dates beginning in D1, apply the rule to the grid below the timeline; Excel colors each cell whose date falls within that task’s range.
What the between-dates formula does
The formula checks two conditions for every cell in the selected schedule grid: the date in the timeline header is on or after the task’s start date, and it is on or before the finish date. AND returns TRUE only when both conditions are true. Conditional formatting uses that result to fill the matching cells, making a horizontal bar for each task.
The dollar signs are important. In D$1, the row is fixed so the formula always checks the header row, while the column can change as the rule moves across the timeline. In $B2 and $C2, the start and finish columns are fixed, but the row can change for each task. This mixed-reference setup lets one rule work across a rectangular range.
The formula does not create a chart object or draw a bar by itself. It identifies which cells should be formatted. The colored cells form the visual schedule, and the task names and dates remain ordinary worksheet data that you can edit.
Set up the task and timeline layout
Put one task on each row. A simple layout is task name in A, start date in B, finish date in C, and a date timeline from D onward. Put the timeline dates in row 1 and task details in rows 2 and below. For example, row 2 might contain Design, January 3, 2026, and January 5, 2026. If the timeline has daily columns, that task should occupy three cells: January 3, 4, and 5.
Make sure the start, finish, and timeline entries are real Excel dates, not text that merely looks like a date. Excel stores dates as serial numbers, which makes comparisons such as greater than or equal to work. You can check a suspicious cell by changing its number format temporarily to General; a real date displays as a number. Enter dates consistently, and use a clear date format such as mmm d or d-mmm so adjacent headers are readable.
Before adding formatting, check the data rules you want to follow. This example treats both endpoints as included: a task starting and finishing on the same day gets one colored cell. If your organization treats the finish date as the first day the task is no longer active, the comparison needs to be exclusive at the finish end instead.
Create the date headers
You can type the first timeline date and fill later dates across, or generate the first date from your task list. With task start dates in B2:B6 and the first timeline header in D1, enter =MIN($B$2:$B$6) in D1. If the earliest start date in that example range is January 3, 2026, D1 returns January 3, 2026. Format the cell as a date so it displays as a date rather than its underlying serial number.
With D1 holding January 3, 2026 and the next daily header in E1, enter =D1+1 in E1 and fill it right. E1 returns January 4, 2026; each later cell advances the timeline by one day. If you prefer to enter dates manually, the headers still need to be consecutive and correctly ordered for the bars to communicate time accurately.
Using MIN makes the chart begin at the earliest listed start date, but it does not automatically decide how many columns the schedule needs. Extend the timeline far enough to include the latest finish date. For a long project, you may prefer to choose a fixed reporting window rather than create an extremely wide worksheet.
Apply conditional formatting to draw the bars
- Select the cells where task bars should appear, starting at the first task row and first timeline column. For example, select D2:AI6 if there are five tasks and the timeline runs through column AI.
- On Excel for Microsoft 365 for Windows, choose Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- With the selection beginning at D2, enter
=AND(D$1>=$B2,D$1<=$C2)in the formula box. - Choose Format, select a fill color on the Fill tab, and confirm the dialogs.
In the example, if B2 is January 3, 2026, C2 is January 5, 2026, and the headers in D1:F1 are January 3 through 5, the rule returns TRUE for D2, E2, and F2. Those cells receive the chosen fill. The next row uses its own dates because the row references adjust as the rule is evaluated.
If the selected range starts in a different cell, update the formula’s relative column and row references to match its top-left cell. For example, if the first bar cell is E3 and the header is in row 2, the rule should start by referring to E$2 and the task’s date cells on row 3. This is a frequent source of bars appearing one row or one column out of place.
Choose inclusive or exclusive finish dates
The standard rule includes the finish date. That convention is natural for many plans: a task scheduled from Monday through Wednesday fills Monday, Tuesday, and Wednesday. For a one-day task with identical start and finish dates, the cell for that date is colored.
Some planning systems represent an interval as starting on one date and ending at the beginning of another. Under that convention, a task with a start of Monday and finish of Wednesday occupies Monday and Tuesday, but not Wednesday. With dates in the header row and start and finish in columns B and C, the corresponding rule is =AND(D$1>=$B2,D$1<$C2). Use this only when the finish date is meant to be excluded; otherwise, the bar will appear one day shorter than expected.
Excel date values do not include a time unless one is entered. If cells contain times as well as dates, date-only comparisons can produce unexpected results near midnight or at the end of a day. For a basic Gantt chart, use date-only values and agree on one endpoint convention before building the schedule.
Use weekly or monthly timeline columns
A daily timeline is easy to understand but can become too wide for a project lasting several months. A weekly view is more compact. Put the first day of each week in the header and decide whether the bar should appear if any portion of the task overlaps that week. With the week’s starting date in E1, task starts in B and finishes in C, and inclusive dates, use =AND(E$1<=$C2,E$1+6>=$B2). This checks whether the week from E1 through E1 plus six days intersects the task’s date range.
For instance, if E1 is Monday, January 5, and a task runs January 9 through January 12, the week overlaps the task because the week ends January 11 and the task starts before that week ends. The bar appears in that week’s column, even though the task continues into the next week. A weekly bar therefore means “some overlap during this week,” not “the task lasts for exactly seven days.” Label headers with week-start dates or week numbers and make the convention clear to readers.
Monthly columns need a similar overlap test, but month lengths vary. One option is to put the first date of each month in the header and compare it with the first day of the next month. The logic is that a task overlaps a month if it starts before the next month begins and finishes on or after the current month begins. Be explicit about whether the header represents a month or a specific day; simply checking whether the header date falls inside the task will miss tasks that start and end between monthly header dates.
Show duration, milestones, and progress
A duration column can help readers verify the dates behind a bar. With a task’s inclusive start date in B2 and finish date in C2, enter =C2-B2+1 in D2. For a January 3 through January 5 task, the result is 3 days. The plus one counts both the start and finish dates; without it, the result would be 2, which is the difference between the date values rather than the number of included calendar days.
If you add a duration column, move the timeline start column and adjust the conditional-formatting formula accordingly. Do not leave the example’s D-column references in place if the timeline has moved to E or another column. The references must point to the actual timeline header and the correct task date columns.
A milestone is usually a single date rather than a span. If its date is stored in B2 and the timeline header is in D1, a separate rule for a milestone marker can use =D$1=$B2. Give that rule a distinct fill or symbol style so readers can distinguish a milestone from a multi-day task. If the same rows mix milestones and tasks, use a type column or separate formatting rules to avoid marking every task’s start as a milestone.
Progress can be represented with a second visual layer, but it requires a progress value and a clear definition of what percentage complete means. A basic date bar shows planned timing, not actual work completed. Do not interpret a colored bar as proof that work is on schedule unless the workbook also tracks actual dates or status and the chart makes that distinction visible.
Common problems and how to fix them
- No cells fill: Check that start dates, finish dates, and headers are numeric dates, that the formula uses the correct columns, and that the rule applies to the intended grid. Also confirm that the finish date is not earlier than the start date.
- Bars are shifted: Check the top-left cell of the selected range. The formula’s relative reference should match that cell’s timeline column and task row. Keep the header row fixed with a dollar sign and the date columns fixed, while allowing the task row to change.
- Only the first task works: If the task row is written as
$B$2and$C$2, every row checks the dates in row 2. Use$B2and$C2so the row can adjust for each task. - The last day is missing: The rule may use a strict less-than sign at the finish date. Use less than or equal for an inclusive finish, or keep the strict comparison only if your schedule intentionally excludes the finish date.
- Dates look correct but compare incorrectly: A date may be stored as text, or the cells may contain hidden time values. Re-enter or convert the values to real dates, then check number formats and the start/finish order.
- Rules conflict or colors differ: Open Home > Conditional Formatting > Manage Rules and check the Applies to range, rule order, and formatting. Remove obsolete rules or correct overlapping ranges if a copied section has accumulated duplicate rules.
When changing a rule, inspect its Applies to range as well as its formula. A correct formula applied only to the first row will not format the rest of the schedule. Conversely, a rule applied to extra summary rows may color cells that are not task bars.
Formula bars versus other Excel approaches
Conditional formatting is usually the simplest approach for a date-grid Gantt chart because it keeps the schedule in cells and updates the bars when dates change. A formula that returns a character such as a block can also create a visible bar, but it places text in every timeline cell and can complicate alignment, printing, or copying. Conditional formatting separates the schedule’s values from its appearance.
Excel also has chart-based approaches, including stacked bar charts. Those can be useful when you need a polished chart axis or want to show a compact summary, but they require helper series and careful formatting to hide the offset before each task. A cell-based Gantt is often easier to edit and annotate; a chart object can be easier to resize and present. Neither approach automatically makes dates accurate: the task data and chosen date conventions still matter.
For a small schedule, a worksheet grid is practical and transparent. For a large plan with dependencies, resource assignments, changing calendars, or status reporting, a dedicated project-management tool may be more suitable. Excel can model such details, but formula-based bars alone do not enforce dependencies or prevent resource conflicts.
Frequently Asked Questions
What is the Excel formula for a Gantt chart between two dates?
For start dates in B, finish dates in C, and timeline headers in row 1, use =AND(D$1>=$B2,D$1<=$C2) as a conditional-formatting formula, assuming D2 is the first bar cell. Apply a fill format to the selected task grid.
Should the Gantt formula use less than or less than or equal?
Use less than or equal to include the finish date as a working day in the displayed bar. Use strict less than only when the finish date is an exclusive boundary and should not be colored.
Can I make a Gantt chart without conditional formatting?
Yes. You can use formulas that return a character, or create a stacked bar chart with helper data. For a date-by-date worksheet schedule, conditional formatting is often more straightforward because it colors cells without filling them with marker text.
Why does my Gantt bar stop one day early?
Check whether the formula uses a strict comparison at the finish date, whether the finish date is a real date, and whether the timeline includes that date. If the chart uses exclusive finish dates by design, the shorter bar may be correct.
Can the same formula work for weekly Gantt columns?
Not unchanged if each header represents a whole week. Use an overlap test comparing the week’s first and last dates with the task’s start and finish, then label the weekly columns so readers understand what each bar means.
Does this work in Excel for Mac or Excel for the web?
The date-comparison formula and conditional-formatting concept also apply in current Excel for Mac and Excel for the web. Menu wording and dialog layouts can differ; on Windows Microsoft 365, the path is Home > Conditional Formatting > New Rule.
Related
- Excel Worksheet vs CSV: Key Differences and When to Use Each
- Excel: How to Work Out the Difference Between Two Numbers
- Excel Work Schedule Template: Build and Customize One
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Shade Under Line Graph: How to Fill the AreaTo shade under a line graph in Excel, change the chart to a 2-D Area chart; Excel fills…
- Excel Pie Chart Not Working? Fix Common Data IssuesIf an Excel pie chart is blank, incomplete, or showing the wrong slices, first check that its source…
- How to Make a Graph in Google Sheets on a ComputerTo make a graph in Google Sheets on a computer, arrange your information in labeled rows or columns,…
- Excel Pivot Table: Show Only Filtered Data in Your Report?Yes. In Excel for Microsoft 365 on Windows, filter a PivotTable field using its dropdown, or add a…
- Excel Pie Chart Labels: Add, Format, and TroubleshootTo add labels to an Excel pie chart, select the chart and choose Chart Design > Add Chart…
- Excel Histogram: How to Specify BinsYes. In Excel for Microsoft 365 on Windows, insert a histogram, right-click its horizontal axis, choose Format Axis,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.