How to Automatically Color Dates by Age in Excel
Question details
The user needs to automatically color-code dates in a spreadsheet based on how many months have passed since the date.

- 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.
Ensure your target cells are formatted as actual date values in your spreadsheet, not as text, otherwise the time calculation formulas will fail.
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.
Highlight the cells containing the dates you want to format. For example, select cells A1 through A100.
Navigate to the Home tab, click on 'Conditional Formatting', select 'New Rule', and choose 'Use a formula to determine which cells to format'.
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.
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.
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.

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. Open Your Data: Launch WPS Spreadsheet and open the document containing your dates.
- 2. Access Conditional Formatting: Highlight your date range, go to the Home tab, click 'Conditional Formatting', and select 'New Rule'.
- 3. Apply Formula Rules: Select 'Use a formula to determine which cells to format', enter your DATEDIF formulas, and customize your fill colors instantly.

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.




