Fix Excel Conditional Formatting for AM and PM Gantt Times
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.

- 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 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.
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.
Select the cells in the Gantt timeline area where the conditional formatting rule should be applied.
Click Home > Conditional Formatting > Manage Rules, then select the rule that controls the AM/PM Gantt fill.
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.

Round Calculated Start and End Times to Prevent Precision Mismatches
Use this when the column reference is already correct but calculated start or end times still fail for PM values.
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. 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. Open Manage Rules: Select the Gantt timeline range, then click Home > Conditional Formatting > Manage Rules.
- 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. 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. Apply and verify AM/PM formatting: Click OK, apply the rule, and check the Gantt chart with both AM and PM start/end times.

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.




