Formula Errors

Fix Excel Conditional Formatting for AM and PM Gantt Times

Steve KSteve K Nov 13, 2026 869 views

Question details

The user needs to correct a conditional formatting formula for a Gantt chart so AM and PM time ranges highlight consistently using the correct timeline column and rounded calculated time values.

Fix Excel Conditional Formatting for AM and PM Gantt Times
Product
WPS Office Spreadsheets (Excel-compatible)
Device & OS
not provided
Scenario
The user is creating or maintaining a Gantt chart that uses conditional formatting to compare timeline header cells against start and end times containing AM/PM values.
Observed behavior
AM ranges display correctly, while PM ranges do not; the formula uses a wrong second column reference and calculated times can differ by tiny floating-point precision.
Before you start

Before editing the rule, open the Conditional Formatting Rules Manager and verify the Gantt timeline range, the start/end time columns, and whether those start/end times are typed or calculated.

Solution 1Recommended

Use the Same Timeline Column in Both Comparisons

Use this first when AM values format but PM values do not, because a mixed column reference such as G$1 causes the second comparison to look at the wrong timeline cell.

The conditional formatting rule must compare both start and end times against the same timeline column for each Gantt cell. A common mistake is changing the second timeline reference to G$1, which breaks PM ranges.

1
Select the Gantt timeline range

Select the cells in the Gantt timeline area where the conditional formatting rule should be applied.

2
Open Conditional Formatting Rules Manager

Click Home > Conditional Formatting > Manage Rules, then select the rule that controls the AM/PM Gantt fill.

3
Edit the formula to use the same timeline column

Replace the formula with =AND(F$1>=$D2,F$1<=$E2), keeping F$1 in both comparisons and matching the actual timeline column in your sheet.

Use the Same Timeline Column in Both Comparisons
AM and PM values now format consistently: The rule compares the same timeline cell to both start and end times, so AM and PM Gantt blocks highlight correctly.

Fix AM/PM Gantt Conditional Formatting in WPS Office Spreadsheets

In WPS Office Spreadsheets, open Conditional Formatting Rules Manager, correct the timeline column reference, and use ROUND for calculated times so Gantt chart highlights work consistently for both AM and PM values.

  1. 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the .xlsx workbook that contains the Gantt chart and its conditional formatting rules.
  2. 2. Open Manage Rules: Select the Gantt timeline range, then click Home > Conditional Formatting > Manage Rules.
  3. 3. Correct the timeline column reference: Edit the rule and set the formula to =AND(F$1>=$D2,F$1<=$E2), using the same timeline column in both comparisons.
  4. 4. Add rounding for calculated times: If PM values still do not highlight, change the formula to =AND(ROUND(F$1,6)>=ROUND($D2,6),ROUND(F$1,6)<=ROUND($E2,6)).
  5. 5. Apply and verify AM/PM formatting: Click OK, apply the rule, and check the Gantt chart with both AM and PM start/end times.
Fix AM and PM Gantt highlight inconsistencies with native Excel-compatible formulasKeep full compatibility with Microsoft Excel .xlsx workbooks and conditional formatting rulesUse WPS Office Spreadsheets free on Windows, macOS, Linux, Android, and iOSSmall, lightweight office suite with a familiar ribbon interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting highlight AM times but not PM times?

In most cases, the second comparison references the wrong timeline column. Edit the rule in Home > Conditional Formatting > Manage Rules and use the same timeline column in both comparisons, such as =AND(F$1>=$D2,F$1<=$E2).

The columns are correct, but calculated PM times still do not highlight. What should I do?

Calculated time values can contain tiny floating-point differences. Wrap both sides of the formula in ROUND to 6 decimal places: =AND(ROUND(F$1,6)>=ROUND($D2,6),ROUND(F$1,6)<=ROUND($E2,6)).

Where can I edit the conditional formatting formula in WPS Office?

Select the Gantt timeline range, go to Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule to modify the formula.

Will this formula work in Microsoft Excel files opened in WPS Office?

Yes. WPS Office Spreadsheets supports Excel formula syntax and conditional formatting, so the corrected formula works in .xlsx workbooks opened in WPS Office or Microsoft Excel.

Can I apply this rule to an entire Gantt chart row?

Yes. Apply the rule to the full timeline range and use mixed references such as F$1 for the timeline row and $D2 or $E2 for the start and end time columns.