Prevent an Extra Day in an Excel Gantt Chart Conditional Formatting Bar
Question details
The user needs to prevent a conditional-formatting Gantt bar from displaying one extra day after its stated end date.

- 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.
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.
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.
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.
Go to Home > Conditional Formatting > Manage Rules, select the rule that colors the Gantt bars, and choose Edit Rule.
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.
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.
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.

Convert start, end, and header values to valid Excel dates
Use this solution if the formula is correct but bars still start or end in unexpected positions.
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. Open the project worksheet: Open the workbook in WPS Spreadsheets and select the cells used for the Gantt timeline.
- 2. Open conditional formatting: Go to Home > Conditional Formatting > New Rule and select the formula-based rule option.
- 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. Choose a bar format: Click Format, choose a fill color for the task bars, and confirm the rule.
- 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.

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.




