Function Problems

How to Highlight Excel Cells Not Updated in 30 Days with Conditional Formatting

Guest Writer Oct 10, 2026 869 views

Question details

The user needs to visually highlight records in an expense spreadsheet that have not been modified or updated within the last 30 days.

How to Highlight Excel Cells Not Updated in 30 Days

Product: Excel
Device & OS: not provided
Scenario: Tracking spreadsheet records and ensuring data stays current by visually flagging outdated entries with a warning format.
Observed behavior: Records lacking an update within a 30-day window need to automatically display a specific warning format, such as a red fill color, based on a last-update date column.

Before you start

Ensure your spreadsheet has a dedicated 'Last Update' column containing valid, static date formats for each record rather than dynamic formulas.

Solution 1Recommended

Use Formula-Based Conditional Formatting with the TODAY Function

Create a custom conditional formatting rule that subtracts the 'Last Update' date from today's date to trigger a highlight if the difference exceeds 30 days.

By utilizing the TODAY() function within a conditional formatting rule, Excel evaluates the exact age of each record dynamically every day.

Because the TODAY function recalculates automatically, it is crucial that the 'Last Update' column contains static dates entered manually or generated via VBA. If you use a dynamic date formula for the update column, the difference will always evaluate to zero.

Add a Last Update column

Add a 'Last Update' column (for example, Column C) next to your expense data and manually enter the exact dates each record was last modified.

Select the target range

Highlight the range of expense cells or entire rows where you want the warning color to appear.

Create a new formatting rule

Navigate to the 'Home' tab on the ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule' from the dropdown menu.

Enter the TODAY formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter =TODAY()-C2>30 (assuming C2 is the first cell containing the update date).

Apply a warning format

Click the 'Format' button, switch to the 'Fill' tab, choose a prominent color like red, and click 'OK' twice to apply the rule.

Use Formula-Based Conditional Formatting with the TODAY Function

Relative vs. Absolute References: Ensure you use a relative row reference in your formula (like C2 instead of C$2) so that Excel adjusts the formula correctly for each subsequent row down the column.

Manage Data Effectively with WPS Spreadsheet

Easily Highlight Outdated Records in WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting and dynamic date functions like TODAY(). You can seamlessly apply formula-based formatting rules to track aging data without any compatibility issues.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your expense spreadsheet containing the last-update dates.
2. Access Conditional Formatting: Select the data range you wish to monitor, go to the 'Home' tab, and click 'Conditional Formatting' > 'New Rule'.
3. Apply the rule: Select 'Use a formula to format cells', input the formula =TODAY()-C2>30, set your preferred fill color under 'Format', and click 'OK'.

Fully compatible with Microsoft Excel (.xlsx) formats and conditional formatting rules.Lightweight and runs smoothly even when evaluating rules across large datasets.Intuitive UI for creating, managing, and editing custom formatting rules.Free to use basic features for everyday data tracking and spreadsheet management.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting blank cells?

Blank cells are evaluated as zero by the spreadsheet. The formula =TODAY()-0 yields a large number (well over 30), triggering the formatting. To prevent this, modify your formula to ignore blanks: =AND(C2<>"", TODAY()-C2>30).

How do I highlight the entire row instead of just the date cell?

To highlight the entire row, select the entire data range before creating the rule and lock the column reference in your formula by adding a dollar sign before the column letter, like =TODAY()-$C2>30.

Can I automatically update the 'Last Update' date when I edit a cell?

Standard formulas cannot automatically stamp static dates upon editing without creating circular reference issues. You will need to either manually press Ctrl + ; (semicolon) to insert the current static date or use a VBA macro (Worksheet_Change event) to timestamp the update automatically.

Excel & Data Problems