Function Problems

How to Use Excel Conditional Formatting for Dates Near One Year Old

Maira Mehtab Sep 28, 2026 868 views

Question details

The user needs to highlight dates in Excel that are within 30 days of reaching one year old in one color, and dates older than one year in a different color.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking aging dates automatically, such as expiration dates, overdue items, or annual reviews.
Observed behavior: The user wants to automatically apply yellow to dates within 335-365 days old and red to dates older than 365 days using conditional formatting formula rules.

Before you start

Ensure your date column contains valid date formats recognized by Excel, rather than plain text, so the TODAY() function can calculate the exact number of days correctly.

Solution 1Recommended

Use Custom Formula Rules in Conditional Formatting

Create custom formula rules using the TODAY() function to evaluate the age of the dates and apply specific highlight colors.

By utilizing the TODAY() function combined with standard subtraction, you can dynamically calculate how many days have passed since a specific date. This ensures your formatting updates automatically every day without manual adjustments.

Select the target data range

Highlight the column or range of dates you want to format. Note the address of the very first cell in your selection (for example, D3).

Open Conditional Formatting

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

Create the rule for dates near one year (Yellow)

Choose 'Use a formula to determine which cells to format'. Enter the formula: =AND(TODAY()-D3>=335,TODAY()-D3<=365). Click the 'Format' button, select a yellow fill color under the Fill tab, and click OK.

Create the rule for dates older than one year (Red)

Click 'Conditional Formatting' > 'New Rule' again. Select the formula option and enter: =TODAY()-D3>365. Click 'Format', choose a red fill color, and click OK to apply.

Check Your Cell References: Always ensure the cell reference in your formula (like D3) exactly matches the very first cell in your currently selected range, otherwise the formatting will be misaligned.

Solution 2

Apply Advanced Formatting in Excel for the Web

If you are using Excel online where the interface differs, use the built-in Highlight Cells rules or switch to the desktop app for advanced formulas.

Track Dates Easily

Highlight Aging Dates Instantly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting formulas, including the TODAY() function, to help you track aging dates seamlessly. It features a familiar interface making rule management highly intuitive.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the dates you wish to track.
2. Select your date range: Highlight the specific cells or entire column containing your date records.
3. Open Conditional Formatting: Go to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'.
4. Apply your custom formulas: Select 'Use a formula to determine which cells to format', enter =TODAY()-D3>365 (replace D3 with your first cell), and set the highlight color.

Free and lightweight office suite for multiple platforms.Fully compatible with Microsoft Excel (.xlsx) formats and formula rules.Intuitive and easy-to-use conditional formatting manager.Supports complex custom rules like AND(), TODAY(), and advanced date math.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applying to the wrong cells?

This usually happens if the cell reference in your custom formula (e.g., D3) does not match the first cell of the range you selected. Double-check your formula to ensure the starting cell reference is correct and not locked with an absolute reference (like $D$3) if it needs to apply down an entire column.

Does the TODAY() formula update automatically?

Yes, the TODAY() function is volatile, meaning it recalculates automatically every time you open the workbook or whenever the sheet calculates. This ensures your highlighting is always accurate based on the current calendar date.

Can I highlight the entire row instead of just the date cell?

Yes. Select the entire data range (e.g., A3:F100) instead of just the date column. Then, in your conditional formatting formula, lock the column reference for the date by adding a dollar sign (e.g., use =$D3 instead of =D3). This tells the software to evaluate column D but apply the resulting format across the entire row.

How do I remove the conditional formatting rules if I make a mistake?

To clear the rules, select the affected cells, navigate to the Home tab, click Conditional Formatting, choose Clear Rules, and then select 'Clear Rules from Selected Cells'.

Excel & Data Problems