Function Problems

How to Apply Conditional Formatting Based on Current Time in Excel

Guest Writer Oct 7, 2026 870 views

Question details

The user wants to use conditional formatting to automatically highlight a cell or row that corresponds to the current time using formulas.

How to Apply Conditional Formatting Based on Current Time in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic daily schedule or time tracker where the time slot matching the present moment is automatically highlighted for easy tracking.
Observed behavior: When attempting to use functions like NOW() or HOUR(), the formula either fails to format anything, highlights multiple incorrect cells, or returns errors because of mismatched time precision and unsorted lookup ranges.

Before you start

Ensure that the time data in your lookup range is formatted correctly as Time (e.g., h:mm) and is sorted in ascending order so that approximate match formulas can function properly.

Solution 1Recommended

Highlight Current Time Using MOD, NOW, and VLOOKUP

Extract the time portion from the current date-time using MOD and NOW, then use an approximate VLOOKUP to find and highlight the nearest matching time block.

In Excel, NOW() returns both the current date and time as a single numeric value. By using MOD(NOW(),1), you isolate just the fractional time portion.

An approximate match VLOOKUP requires your time list to be sorted from earliest to latest. It will highlight the largest time value that is less than or equal to the current time.

Select the target range

Highlight the data range you want to apply the conditional formatting to (for example, A4:D14). Ensure the time values are in the first column of this range.

Open Conditional Formatting

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

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the VLOOKUP formula

Type the formula: =$A4=VLOOKUP(MOD(NOW(),1),$A$4:$A$14,1,TRUE). Make sure to adjust $A4 to represent the first row of your selected range, and $A$4:$A$14 to represent your absolute time list.

Apply formatting

Click the 'Format' button, switch to the 'Fill' tab, choose your desired highlight color, and click 'OK' twice to apply the rule.

Highlight Current Time Using MOD, NOW, and VLOOKUP

Matching Precision: Using MOD(NOW(),1) prevents the errors associated with TIMEVALUE, which only accepts text values, ensuring a smooth numeric comparison.

Solution 2

Highlight Current Hour Using the HOUR Function

If you only need to match the current hour rather than exact minute precision, you can compare the hour of your cells directly to the current time.

Advanced Spreadsheet Tool

Highlight Dynamic Times Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting and precise time functions like NOW() and VLOOKUP. Build dynamic schedules and track active time blocks effortlessly in a highly compatible environment.

1. Open your schedule: Launch WPS Spreadsheet and open your schedule workbook.
2. Set up Conditional Formatting: Highlight your target time cells, navigate to the Home tab, and click 'Conditional Formatting' > 'New Rule'.
3. Apply the time formula: Select the formula option, enter =$A4=VLOOKUP(MOD(NOW(),1),$A$4:$A$14,1,TRUE), and choose a fill color.
4. Refresh manually when needed: Press F9 on your keyboard anytime you wish to force the NOW() function to recalculate and move the highlight to the present time.

100% compatibility with Microsoft Excel formulas and conditional formatting rulesLightweight design ensuring smooth performance when calculating volatile functions like NOW()Intuitive interface for easily managing and previewing complex dynamic scheduling rules

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula highlight multiple incorrect time cells?

This usually happens due to time precision discrepancies or comparing partial time strings. Using HOUR() alone highlights all entries within that hour. To prevent this, compare complete time values and ensure you use a sorted lookup range with a VLOOKUP approximate match.

Why isn't NOW() updating the conditional formatting automatically in real-time?

The NOW() function is volatile, meaning it recalculates when the worksheet recalculates (such as when you edit a cell). It does not update continuously in real-time on a static screen. You can force a refresh by pressing the F9 key.

Can I use TIMEVALUE(NOW()) instead of MOD(NOW(),1)?

No. The TIMEVALUE function converts a text string representing a time into a decimal. Because NOW() returns a numeric serial value rather than text, nesting it inside TIMEVALUE will result in an error. MOD(NOW(),1) is the correct way to isolate the time decimal from the date integer.

Excel & Data Problems