Function Problems

How to Apply Excel Conditional Formatting for Times Later Than 3 PM

Chanuka Geekiyanage Oct 9, 2026 869 views

Question details

The user wants to create a conditional formatting rule to automatically highlight an arrival-time cell in red if the recorded time is 3:01 PM or later.

How to Apply Excel Conditional Formatting for Times Later Than 3 PM

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Monitoring arrival times or schedules by highlighting any time entries that exceed a specific 3:00 PM cutoff threshold.
Observed behavior: Cells need to automatically turn red when the time value exceeds 3:00 PM, but the formula required changes depending on whether the cell contains standard time values or numeric equivalents like 1500.

Before you start

Before setting up your conditional formatting rule, verify whether your arrival-time column contains standard Excel time formats (like 3:00 PM) or plain numeric values using a custom hhmm format (like 1500).

Solution 1Recommended

Highlight Standard Excel Time Values (Real Time)

Use this method if your spreadsheet records time using actual time formats recognized by the software.

When your cells contain real time values, the conditional formatting rule must use a time-specific formula like the TIME function to accurately compare the data against your 3:00 PM threshold.

Select the arrival-time cells

Click and drag to highlight the range of cells containing the arrival times you want to format.

Open Conditional Formatting

Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select New Rule from the drop-down menu.

Set the cell condition

Choose 'Format only cells that contain'. In the rule description, set the first dropdown to 'Cell Value' and the second to 'greater than'. In the value field, type the formula =TIME(15,0,0).

Apply a red fill color

Click the Format button, go to the Fill tab, select a red background color, and click OK to apply the rule.

Highlight Standard Excel Time Values (Real Time)

Alternative Time Formulas: Instead of =TIME(15,0,0), you can also use =TIMEVALUE("15:00") or =TIMEVALUE("3 pm") in the rule field.

Solution 2

Highlight Custom Numeric Time Values

Use this method if your time data is entered as pure numbers formatted to look like time, such as entering 1500 for 3:00 PM.

Manage Spreadsheets Efficiently

Easily Highlight Time Data Using WPS Spreadsheet

WPS Spreadsheet provides highly intuitive conditional formatting tools that easily handle both standard and custom numeric time formats, allowing you to highlight critical data thresholds instantly.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your arrival time tracking data.
2. Access Conditional Formatting: Highlight your target cells, go to the Home tab, and click the Conditional Formatting icon.
3. Apply the time rule: Select 'Highlight Cells Rules' > 'Greater Than', enter =TIME(15,0,0), choose the light red fill option, and click OK.

Free and lightweight spreadsheet softwareFully compatible with Microsoft Excel file formats (.xlsx, .xls)Intuitive Conditional Formatting menu for fast data visualizationSeamless processing of complex time and date formulas

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the TIMEVALUE function instead of the TIME function for this rule?

Yes, you can use the TIMEVALUE function. When setting up your conditional formatting rule, simply enter =TIMEVALUE("15:00") or =TIMEVALUE("3 pm") in the value field. It achieves the exact same result as =TIME(15,0,0).

Why isn't my conditional formatting working on custom 'hhmm' formatted cells?

If you typed a number like 1435 and used a custom cell format to make it look like a time, the spreadsheet still treats the underlying data as the integer one thousand four hundred and thirty-five. Time functions like TIME(15,0,0) resolve to a fraction of 1, which will never trigger correctly against large integers. You must format the rule to be strictly 'greater than 1500'.

How do I remove the conditional formatting rule if I no longer need it?

Select the cells containing the formatting you wish to remove, navigate to Home > Conditional Formatting > Clear Rules, and choose 'Clear Rules from Selected Cells'.

Excel & Data Problems