Chart & Visualization Issues

Prevent an Extra Day in an Excel Gantt Chart Conditional Formatting Bar

Chanuka GeekiyanageChanuka Geekiyanage Nov 12, 2026 868 views

Question details

The user needs to prevent a conditional-formatting Gantt bar from displaying one extra day after its stated end date.

Prevent an Extra Day in an Excel Gantt Chart Conditional Formatting Bar
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet uses dates in the header row and conditional formatting to color cells between each task's start and end dates.
Observed behavior
The Gantt bar extends into the day after the task end date because the formula evaluates the previous date column instead of the current one.
Before you start

Verify that the date in each Gantt chart header cell and the task start and end values are real Excel dates, not text. Identify the first date cell in the conditional-formatting applied range before editing the rule.

Solution 1Recommended

Use the current header date in the Gantt chart conditional-formatting rule

Use this solution when a Gantt bar includes the day after the end date because the rule references the previous header column.

The conditional-formatting formula must compare each task's start and end dates with the date at the top of the same column being formatted. If the header date is in row 2 and the first chart column is M, use M$2 rather than L$2.

1
Select the Gantt chart cells

In Excel, select the full timeline area where task bars should appear, such as M3:AZ100. Do not include the date-header row in the selection.

2
Open the conditional-formatting rule

Go to Home > Conditional Formatting > Manage Rules, select the rule that colors the Gantt bars, and choose Edit Rule.

3
Enter the corrected formula

Choose Use a formula to determine which cells to format and enter =AND($E3<=M$2,$F3>=M$2). Replace M with the first column of your selected chart range, while keeping the start and end date columns fixed as needed.

4
Confirm the Applies to range

In Conditional Formatting Rules Manager, set Applies to to the complete Gantt chart range, such as =$M$3:$AZ$100, then click Apply and OK.

5
Test the end date

Check a task with a known end date. Its final colored cell should be under that end date, and the following date column should remain unformatted.

Use the current header date in the Gantt chart conditional-formatting rule
Why the corrected formula works: M$2 refers to the date in the current chart column. Using L$2 would test the prior column's date and can color the next day's cell.
Create accurate timeline bars in WPS Spreadsheets

Build an End-Date-Accurate Gantt Chart in WPS Spreadsheets

WPS Spreadsheets lets you create a date-based Gantt chart with formula-driven conditional formatting. Compare each task's start and end date with the current timeline header cell so the highlighted bar ends on the correct day.

  1. 1. Open the project worksheet: Open the workbook in WPS Spreadsheets and select the cells used for the Gantt timeline.
  2. 2. Open conditional formatting: Go to Home > Conditional Formatting > New Rule and select the formula-based rule option.
  3. 3. Add the date-range formula: Enter a formula such as =AND($E3<=M$2,$F3>=M$2), using the first timeline column and the correct start and end date columns for your sheet.
  4. 4. Choose a bar format: Click Format, choose a fill color for the task bars, and confirm the rule.
  5. 5. Apply the rule across the timeline: Set the rule to the full Gantt chart range and verify that each bar stops under its task end date.
Create Gantt bars with conditional-formatting formulas based on task start and end dates.Use the current timeline header date to avoid an extra highlighted day.Open and edit Microsoft Excel .xlsx and .xls Gantt chart workbooks.Work in a lightweight spreadsheet interface with familiar formatting and formula tools.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel Gantt chart show one day after the end date?

This usually happens when the conditional-formatting formula references the previous header cell, such as L$2, instead of the current chart column's header date, such as M$2.

What formula should I use to color cells between a start and end date?

If the start date is in E3, the end date is in F3, and the first timeline header is M2, use =AND($E3<=M$2,$F3>=M$2). Apply it beginning with the matching first timeline column.

Should the Gantt chart end date be included in the colored bar?

Yes. The >= comparison includes the cell whose header date equals the end date. The next column remains uncolored because its date is later than the end date.

How do I edit an existing conditional-formatting formula in Excel?

Select a cell in the Gantt chart, then go to Home > Conditional Formatting > Manage Rules. Select the rule, click Edit Rule, update the formula, and check the Applies to range.

Why does the Gantt formatting still fail after I change the formula?

Check that the start dates, end dates, and header dates are actual Excel date values rather than text. You can inspect them through Home > Number Format or convert text dates with Data > Text to Columns.