How to Highlight Excel Times More Than Five Minutes Apart in WPS Office Spreadsheets
Question details
The user needs a conditional formatting rule that compares times in two columns and highlights both cells when the absolute difference is greater than five minutes.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet has times in two columns, such as scheduled vs. actual times, and the user wants to flag any row where the two times are more than five minutes apart.
- Observed behavior
- The desired state is a rule that applies red fill to both time cells in a row when =ABS($A1-$B1)>TIME(0,5,0) evaluates to TRUE.
Open the workbook in WPS Office Spreadsheets and confirm that both time columns are formatted as real time values, not text. Then select the exact range containing the two time columns, for example A1:B100, before creating the conditional formatting rule.
Create a formula-based conditional formatting rule with ABS and TIME
Use this when you need to compare two time columns row by row and highlight both cells in red whenever the difference exceeds five minutes.
The formula =ABS($A1-$B1)>TIME(0,5,0) calculates the absolute difference between the two times, so it works regardless of which column is earlier. The $A and $B column references keep the comparison anchored to columns A and B while the row number adjusts for each row in the selected range.
In WPS Office Spreadsheets, select the range that contains both time columns—for example, A1:B100—so the conditional formatting rule will apply to every row in the selection.
Go to the Home tab, click Conditional Formatting, and choose New Rule. Then select Use a formula to determine which cells to format.
In the formula box, type =ABS($A1-$B1)>TIME(0,5,0). This returns TRUE when the absolute difference between the two time values is more than five minutes.
Click Format, open the Fill tab, choose Red or a red fill color, and click OK to close the Format Cells dialog.
Click OK in the New Formatting Rule dialog, then test with sample times such as 7:50 in A1 and 7:56 in B1. Both cells should now be highlighted red.
Highlight time differences over five minutes with WPS Office Spreadsheets
Use a formula-based conditional formatting rule in WPS Office Spreadsheets to compare two time columns and automatically apply a red fill when the absolute difference exceeds five minutes. The familiar ribbon and Excel-compatible rule engine make the same workflow easy and free.
- 1. Select the time columns: Open the workbook in WPS Office Spreadsheets and select the range containing both time columns, such as A1:B100.
- 2. Create a formula-based rule: Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
- 3. Enter the comparison formula: Type =ABS($A1-$B1)>TIME(0,5,0) in the formula box to compare each row's two time values.
- 4. Apply red fill and confirm: Click Format, choose a red fill color on the Fill tab, and confirm to apply the rule to the selected range.
Frequently Asked Questions
Why should I use ABS in the conditional formatting formula?
ABS calculates the absolute difference, so the rule works whether the time in column A is earlier or later than column B. For example, A=7:50 and B=7:56 or A=7:56 and B=7:50 both trigger the highlight.
Which cells should I select before creating the rule?
Select the entire two-column range, such as A1:B100. The formula uses $A and $B column references, and the active cell row must match the first row of the selected range for the rule to evaluate correctly.
What if my times are not recognized as time values?
In WPS Office Spreadsheets, select the cells and check the Number Format box on the Home tab. If they are formatted as Text, change them to Time, then re-enter or recalculate the values so the formula can compare them.
Can I highlight only rows where the difference is more than 10 minutes?
Yes, change TIME(0,5,0) to TIME(0,10,0) in the same formula. The rule will then highlight both cells when the absolute difference between the two times exceeds ten minutes.
Does this rule work in Microsoft Excel too?
The formula is compatible between WPS Office Spreadsheets and Excel, so the same conditional formatting rule can be applied in either application and remains intact in XLS and XLSX files.
