How to Highlight Break Time Cells When a Break Exceeds 36 Minutes in WPS Office Spreadsheets
Question details
The user wants to apply conditional formatting in WPS Office Spreadsheets so that both an OUT time cell and an IN time cell are highlighted red when the break duration exceeds 36 minutes.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a break log with OUT and IN time cells, likely in Excel or WPS Spreadsheets, and needs a visual alert when a break exceeds 36 minutes.
- Observed behavior
- Both the OUT and IN cells should turn red when the IN minus OUT duration is greater than 36 minutes; otherwise no highlight is applied.
Confirm that the OUT and IN cells contain valid serial time values rather than text, and decide whether you are formatting one row or a larger table range before creating the rule.
Apply One Formula-Based Rule to Highlight Both OUT and IN Cells
Use this method when the OUT time is in one column, such as C, and the IN time is in the next column, such as D, and you want both cells in the same row to turn red when the break exceeds 36 minutes.
The formula locks the OUT and IN columns with dollar signs but keeps the row reference relative. This allows one rule to cover multiple rows without creating a separate rule for each row.
Start from C8 so it is the active cell, then select the range that contains both the OUT and IN cells, for example C8:D100. If you are formatting only row 8, select C8:D8.
Go to the Home tab, click Conditional Formatting, and select New Rule.
Choose "Use a formula to determine which cells to format" and enter: =AND($C8<>"",$D8<>"",$D8-$C8>TIME(0,36,0)).
Click Format, open the Fill tab or Font tab, choose red, and click OK to close the Format Cells and New Formatting Rule dialogs.
Use a Column-Locked Formula for Break Logs with OUT and IN in D and E
Use this variation when Break 1 OUT is in column D and IN is in column E, or when you prefer a shorter formula that only checks the time difference.
Create Break Time Alerts with Conditional Formatting in WPS Office
WPS Office Spreadsheets can accomplish this directly with a formula-based conditional formatting rule. It uses the same ribbon-style Home tab logic as Excel, so you can highlight OUT and IN cells red when break duration exceeds 36 minutes without changing your workbook.
- 1. Open your workbook: Open the Excel or WPS workbook that contains the OUT and IN break time columns in WPS Office Spreadsheets.
- 2. Select the break cells: Select the OUT and IN range, such as C8:D100, with the OUT cell as the active cell.
- 3. Create a conditional formatting rule: Click Home > Conditional Formatting > New Rule, then choose "Use a formula to determine which cells to format."
- 4. Enter the formula and format: Enter =AND($C8<>"",$D8<>"",$D8-$C8>TIME(0,36,0)), click Format, select red, and confirm.
- 5. Apply and review: Click OK to apply the rule and verify that any break longer than 36 minutes highlights both cells in red.
Frequently Asked Questions
Do I need a separate rule for each row of break times?
No. Use a formula with row-relative references and lock only the columns, such as =AND($C8<>"",$D8<>"",$D8-$C8>TIME(0,36,0)), and apply it to the full range C8:D100.
Why does the rule highlight only one cell instead of both OUT and IN cells?
This usually happens when the formula does not lock the column letters or when only one cell is selected. Select the two-column range and use $C8 and $D8 so the rule evaluates both cells in the same row.
Can I use this conditional formatting rule in Microsoft Excel too?
Yes. The formula and rule type are compatible with Excel and WPS Office Spreadsheets; the file can be saved in .xlsx format without losing the conditional formatting.
How do I change the threshold from 36 minutes to another time?
Open Conditional Formatting > Manage Rules, edit the rule, and replace TIME(0,36,0) with another time, for example TIME(0,45,0) for 45 minutes.
The formula is not highlighting rows with blank time cells. Is that expected?
With the AND formula that checks C8<>"" and D8<>"", blank cells are intentionally ignored. If you use the shorter difference formula, blank cells also evaluate to 0 and are not highlighted.
