Formatting Issues

How to Automatically Color Dates by Age in Excel

Adam DavisAdam Davis Sep 30, 2026 873 views

Question details

The user needs to automatically color-code dates in a spreadsheet based on how many months have passed since the date.

How to Automatically Color Dates by Age in Excel
Product
Microsoft Excel / WPS Spreadsheet
Device & OS
not provided
Scenario
Tracking elapsed time for employee competency, training dates, or project deadlines by applying visual color cues to dates.
Observed behavior
Dates need to display specific fill colors (red, yellow, or green) dynamically based on their age in months compared to today's date.
Before you start

Ensure your target cells are formatted as actual date values in your spreadsheet, not as text, otherwise the time calculation formulas will fail.

Solution 1Recommended

Use Conditional Formatting with the DATEDIF Formula

Apply custom formula rules in Conditional Formatting to highlight dates based on their age in months.

By combining Conditional Formatting with the DATEDIF function, you can calculate the exact difference in months between a specified date and today's date. Setting up multiple rules allows you to assign a traffic-light color system (red, yellow, green) based on aging thresholds.

1
Select the target date range

Highlight the cells containing the dates you want to format. For example, select cells A1 through A100.

2
Create a new formatting rule

Navigate to the Home tab, click on 'Conditional Formatting', select 'New Rule', and choose 'Use a formula to determine which cells to format'.

3
Enter the formula for the oldest dates (Red)

In the formula box, type =DATEDIF($A1,TODAY(),"m")>=24 to target dates 24 months or older. Click 'Format', select the Fill tab, choose a Red background color, and click OK.

4
Add rules for Yellow and Green categories

Repeat the previous step twice to create two new rules. For the Yellow rule, use =DATEDIF($A1,TODAY(),"m")>=22. For the Green rule, use =DATEDIF($A1,TODAY(),"m")<22.

5
Manage rule hierarchy

Go to 'Conditional Formatting' > 'Manage Rules'. Ensure the rules are ordered from top to bottom as Red, Yellow, then Green. Check the 'Stop If True' box for the Red and Yellow rules to prevent them from being overwritten.

Use Conditional Formatting with the DATEDIF Formula
Cell Reference Tip: Ensure you use a mixed reference like $A1 instead of an absolute reference like $A$1. This allows the formula to adapt dynamically to each row in your selected range.
Efficient Spreadsheet Management

Easily Color-Code Dates with WPS Spreadsheet

WPS Spreadsheet provides robust conditional formatting tools and full support for advanced Excel formulas like DATEDIF, making it incredibly easy to track deadlines and age out dates automatically.

  1. 1. Open Your Data: Launch WPS Spreadsheet and open the document containing your dates.
  2. 2. Access Conditional Formatting: Highlight your date range, go to the Home tab, click 'Conditional Formatting', and select 'New Rule'.
  3. 3. Apply Formula Rules: Select 'Use a formula to determine which cells to format', enter your DATEDIF formulas, and customize your fill colors instantly.
Fully compatible with Microsoft Excel conditional formatting and formulas.Intuitive rule manager interface for handling multiple overlapping color rules.Free, lightweight, and fast alternative for powerful data analysis and tracking.
microsoft office alternative - wps office

Frequently Asked Questions

Why is the DATEDIF formula returning a #VALUE! error?

If DATEDIF returns a #VALUE! error, ensure the starting date cell is formatted as a valid date and is chronologically earlier than today's date. DATEDIF cannot calculate a negative duration.

Can I color dates based on age in days instead of months?

Yes, you can modify the formula by changing the "m" (months) argument to "d" (days). For example, use =DATEDIF($A1,TODAY(),"d")>30 to highlight dates older than 30 days.

Why is the entire column turning the same color regardless of the date?

This usually happens if you used an absolute reference (like $A$1) in your conditional formatting formula. Update the formula in the Rules Manager to use a mixed reference (like $A1) so the rule evaluates each row individually.