Formula Errors

How to Highlight Excel Timestamps Less Than Two Hours Old

Maira Mehtab Sep 28, 2026 870 views

Question details

The user wants to automatically highlight spreadsheet cells containing timestamps that fall within the last two hours compared to the current time.

Product: Excel
Device & OS: not provided
Scenario: Tracking recent data entries, log events, or timestamps that require visual emphasis if they occurred within the previous two hours.
Observed behavior: Using conditional formatting with a custom formula comparing the cell value to the current time to apply dynamic highlighting.

Before you start

Ensure your timestamp data is properly formatted as recognizable dates and times (e.g., mm/dd/yyyy hh:mm) so that the spreadsheet can accurately calculate the time difference.

Solution 1Recommended

Use Conditional Formatting with the NOW Function

Apply a custom formula rule in Conditional Formatting to check if the timestamp is within the past 2 hours.

By combining the AND and NOW functions, you can subtract the cell's timestamp from the current time. Because the software stores dates and times as fractions of a day, a two-hour window is represented mathematically as 2/24.

Select the data range

Highlight the range of cells containing your timestamps (for example, A2:A100).

Open Conditional Formatting

Navigate to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.

Choose formula rule

Select the option that says 'Use a formula to determine which cells to format'.

Enter the time calculation formula

Assuming A2 is the first cell in your selected range, enter the formula: =AND(A2<>"",NOW()-A2<2/24)

Apply a highlight format

Click the 'Format' button, go to the 'Fill' tab to pick a background color, and click 'OK' twice to apply the rule.

Refreshing the calculation: The NOW() function recalculates periodically or whenever the worksheet recalculates. You can press F9 at any time to force a refresh and update the highlighted timestamps.

Smart Data Formatting

Highlight Timestamps Easily in WPS Spreadsheet

WPS Office Spreadsheet fully supports advanced conditional formatting and dynamic time-based formulas. You can seamlessly track recent data entries using the exact same formulas as Microsoft Excel.

1. Select your data: Open your workbook in WPS Spreadsheet and select the cells containing your timestamps.
2. Create a new rule: Go to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.
3. Input the formula: Choose the formula option and type: =AND(A2<>"",NOW()-A2<2/24)
4. Format and save: Set your desired highlight color in the Format menu and click OK to apply.

100% compatibility with Microsoft Excel (.xlsx) conditional formatting rules.Familiar ribbon interface makes it easy to find and apply Conditional Formatting tools.Lightweight and fast, effortlessly processing rules across large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting blank cells?

If you only use the formula NOW()-A2<2/24, empty cells are treated as zero, which makes the formula evaluate as true. Adding A2<>"" inside the AND function ensures that completely blank cells are ignored.

Can I alter the formula to highlight timestamps from the last 24 hours?

Yes. To highlight timestamps within the last 24 hours (which equals 1 full day), change the time fraction in the formula from 2/24 to 1. The formula would be: =AND(A2<>"",NOW()-A2<1).

How do I highlight times in the future instead of the past?

To highlight future timestamps that will occur within the next two hours, you must reverse the subtraction order and ensure the time hasn't passed yet. Use this formula: =AND(A2<>"",A2-NOW()<2/24,A2>NOW()).

Excel & Data Problems