How to Highlight the Current Time Slot with Conditional Formatting in WPS Spreadsheet
Question details
The user wants to apply a conditional formatting formula to a two-hour schedule so the cell whose start and end times contain the current time is highlighted and the highlight changes automatically as time passes.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing an hourly and half-hour schedule in a spreadsheet and wanting the active time slot to stand out automatically.
- Observed behavior
- The correct slot should be highlighted based on the current time and refresh when the workbook recalculates.
Before creating the rule, make sure the schedule start and end times are stored in separate cells (for example, start in E6 and end in H6) and are formatted as real time values, not text.
Create a Formula-Based Conditional Formatting Rule with MOD(NOW(),1)
Use this solution when your schedule has start and end times in separate columns and you want the matching time slot to highlight automatically.
Use MOD(NOW(),1) instead of NOW() alone. NOW() returns the date and time, while MOD(NOW(),1) keeps only the fractional time value so it can be compared with time-only cells.
Keep the row reference relative so the same rule can evaluate each row in the selected schedule range.
In the first schedule row, note the cell that contains the slot start time (for example E6) and the cell that contains the slot end time (for example H6).
Select the cells or rows in the schedule that should receive the highlight, starting from the first data row so the formula references match.
Go to Home > Conditional Formatting > New Rule, then select “Use a formula to determine which cells to format.”
In the formula box, enter =($E6<MOD(NOW(),1))*($H6>MOD(NOW(),1)) and replace E6 and H6 with your actual start and end cells if they are different.
Click Format, choose a fill color or font style, confirm the rule, and click OK to apply the highlight to the selected range.
Make the Highlight Refresh Automatically
Use this solution if the highlighted slot does not update immediately while the schedule is open.
Highlight the Current Time Slot Automatically in WPS Spreadsheet
WPS Spreadsheet supports formula-based conditional formatting, so you can use MOD(NOW(),1) to compare the current time with schedule start and end times and highlight the active slot without needing a paid Office subscription.
- 1. Open the schedule in WPS Spreadsheet: Launch WPS Office, open the workbook that contains the hourly or half-hour schedule, and go to the worksheet with the time slots.
- 2. Select the slot cells: Select the range that should receive the highlight, starting with the first schedule row so the formula reference matches the active row.
- 3. Create a formula-based rule: Click Home > Conditional Formatting > New Rule, choose “Use a formula to determine which cells to format,” and enter =($E6<MOD(NOW(),1))*($H6>MOD(NOW(),1)).
- 4. Choose a highlight format: Click Format, pick a fill color or font style, confirm the rule, and apply it to the selected schedule range.
- 5. Test the automatic refresh: Press F9 to recalculate or wait for the time to cross a slot boundary, then verify that the correct slot is highlighted.
Frequently Asked Questions
Why should I use MOD(NOW(),1) instead of NOW() in the conditional formatting formula?
NOW() returns both the date and the time, but time-only cells are stored as decimal fractions of a 24-hour day. MOD(NOW(),1) isolates the fractional time part so it can be compared correctly with schedule start and end times.
How do I make the current time slot highlight refresh automatically?
Go to Formulas > Calculation Options and make sure Automatic is selected. NOW() is volatile, so it recalculates when the workbook recalculates; you can also press F9 to force a refresh immediately.
Can I highlight an entire row instead of a single cell?
Yes. Select the entire schedule range or the rows you want to format, and use the same formula with relative row references and absolute column references for the start and end times, such as =($E6<MOD(NOW(),1))*($H6>MOD(NOW(),1)).
What should I change if my start and end times are not in columns E and H?
Replace $E6 and $H6 with the actual cell references for your start and end time columns. Keep the dollar sign before the column letter and use a relative row number so the rule works for each schedule row.
Does this formula work in both Microsoft Excel and WPS Spreadsheet?
Yes. The formula is standard and works in both applications. WPS Spreadsheet also supports the same Home > Conditional Formatting > New Rule workflow, so the file remains compatible with Excel.
