Calculation Issues

Excel Conditional Formatting: Turn Row Yellow After 2 Hours and Red at Deadline

Algirdas Jasaitis Sep 29, 2026 869 views

Question details

The user wants to apply conditional formatting rules that automatically change a row's color based on specific time thresholds: yellow two hours after an original timestamp, and red after a designated deadline.

How to Turn a Row Yellow After Two Hours and Red at the Deadline in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking time-sensitive tasks where visual cues are needed to indicate approaching deadlines and overdue statuses.
Observed behavior: Rows need to dynamically evaluate the current time against stored timestamps and due dates to apply the correct background color.

Before you start

Ensure your timestamp and due date cells contain valid Excel date-and-time values (not plain text) so the time-based formulas can calculate the differences accurately.

Solution 1Recommended

Use NOW() Formulas in Conditional Formatting Rules

Apply specific formulas using the NOW() function to dynamically format rows as deadlines approach or pass.

Excel's NOW() function returns the current system date and time. By comparing this real-time value to your timestamp and deadline cells, you can trigger formatting changes automatically. Note that Excel recalculates NOW() periodically or when the sheet is modified, so color updates might not be instantaneous without a refresh.

Select the target range

Highlight the entire row or range of rows where you want the conditional formatting to apply.

Create the Red deadline rule

Navigate to Home > Conditional Formatting > New Rule. Choose 'Use a formula to determine which cells to format'. Enter the formula =NOW()>$D$3 (assuming D3 is your due date cell). Click Format, choose a Red fill color, and click OK.

Create the Yellow two-hour rule

Repeat the process to add a second rule. Select 'Use a formula to determine which cells to format' and enter =NOW()>($C$3+(2/24)) (assuming C3 is the original timestamp). Click Format, choose a Yellow fill color, and click OK.

Set rule priority and Stop If True

Go to Home > Conditional Formatting > Manage Rules. Ensure the Red rule is positioned above the Yellow rule in the list. Check the 'Stop If True' box next to the Red rule, then click Apply and OK.

Use NOW() Formulas in Conditional Formatting Rules

Applying to Multiple Rows: If you are applying this rule to an entire table instead of a single row, use mixed references (e.g., $D3 instead of $D$3) so the formula evaluates each row's specific dates correctly.

Advanced Formatting Made Easy

Create Time-Based Conditional Formatting Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting formulas, including the NOW() function. You can easily set up dynamic color rules to visually track your deadlines and time-sensitive tasks without any hassle.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your timestamps and deadlines.
2. Access Conditional Formatting: Select the rows you want to format, go to the Home tab, click Conditional Formatting, and select New Rule.
3. Input the NOW() formulas: Select 'Use a formula to determine which cells to format' and enter your formulas for both the red and yellow conditions.
4. Manage rule order: Use the Manage Rules option to place the overdue (red) rule at the top and check the 'Stop If True' box to ensure proper color display.

Fully compatible with Microsoft Excel conditional formatting rules and formulas.Intuitive rule manager to easily reorder priorities and apply 'Stop If True' conditions.Lightweight and fast performance, ensuring smooth calculations even with dynamic real-time formulas.

microsoft office alternative - wps office

Frequently Asked Questions

Why aren't my rows changing color exactly at the two-hour mark?

Volatile functions like NOW() only update when the Excel worksheet recalculates. You can force a manual recalculation at any time by pressing the F9 key or by editing any cell.

How do I add exactly two hours to a time in Excel formulas?

Excel stores dates and times as numbers where 1 equals one full day (24 hours). To add two hours to a time, you add the fraction (2/24) to the original cell reference.

What does the 'Stop If True' checkbox do in Conditional Formatting?

It prevents lower-priority rules from being evaluated if a higher-priority rule's condition is met. In this scenario, if the deadline has passed (Red), 'Stop If True' ensures it stays red and doesn't also get evaluated by the lower-priority Yellow rule.

Excel & Data Problems