Formatting Issues

How to Automatically Change Excel Cell Color by Years of Service

John Wilson Oct 10, 2026 869 views

Question details

The user needs to automatically color-code employee records in Excel based on their years of service milestones (such as one, three, and five years).

Automatically Change Excel Cell Color by Years of Service

Product: Excel
Device & OS: not provided
Scenario: Tracking employee service milestones where cell formatting updates dynamically as time passes.
Observed behavior: To achieve dynamic color-coding based on the current date and the employee's start date without manual updates.

Before you start

Ensure you have a column containing the employees' start dates formatted correctly as dates in Excel before applying the conditional rules.

Solution 1Recommended

Apply Conditional Formatting with Date Formulas

Use the Conditional Formatting feature with EDATE and TODAY functions to dynamically color cells based on elapsed time.

By utilizing the EDATE function in combination with TODAY(), you can calculate exact thresholds for past dates, enabling Excel to format cells when a specific number of months has passed.

Select the Target Cells

Highlight the cells containing the employee start dates that you want to format.

Create a New Rule

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

Define the First Milestone

Choose 'Format only cells that contain'. Leave the first dropdown as 'Cell Value' and change the second to 'less than or equal to'. In the formula box, enter =EDATE(TODAY(),-12) for employees with one year or more of service.

Set the Cell Format

Click the Format button, go to the Fill tab, select your preferred color for the one-year milestone, and click OK.

Add Additional Rules

Repeat the process to create additional rules for other milestones. For a three-year milestone, use the formula =EDATE(TODAY(),-36) and choose a different fill color.

Apply Conditional Formatting with Date Formulas

Rule Priority: When applying multiple conditional formatting rules, ensure they are ordered correctly in the 'Manage Rules' dialog. Excel applies rules in order of priority, so overlapping conditions might require checking the 'Stop If True' box.

Professional Spreadsheet Software

Easily Track Milestones with WPS Spreadsheet

WPS Spreadsheet fully supports complex conditional formatting and date functions like EDATE and TODAY, allowing you to seamlessly manage employee data and track service milestones dynamically.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the file containing your employee records.
2. Select Start Dates: Highlight the column or range of cells containing the employee start dates.
3. Access Conditional Formatting: Go to the Home tab, click on Conditional Formatting, and select New Rule.
4. Apply Date Formulas: Select 'Format only cells that contain', set the condition to 'less than or equal to', and input =EDATE(TODAY(), -12) to assign colors based on service years.

Fully compatible with Microsoft Excel formulas and conditional formatting.Easily highlight important dates and track milestones automatically.Free and lightweight spreadsheet tool for efficient data management.Familiar user interface makes tracking schedules simple.

Frequently Asked Questions

Why are all my cells turning the same color despite different years of service?

This usually occurs due to rule priority. Go to Conditional Formatting > Manage Rules, and ensure your largest threshold (e.g., 5 years) is evaluated properly by either placing it at the top with 'Stop If True' checked, or by strictly defining 'between' ranges instead of 'less than or equal to'.

Does this formatting automatically update every day?

Yes, because the TODAY() function is volatile, it recalculates every time you open the workbook or change a cell. This ensures the service years and colors are always up to date without manual adjustments.

How do I remove the conditional formatting if I no longer need it?

Select the formatted cells, navigate to the Home tab, click Conditional Formatting, choose Clear Rules, and then select Clear Rules from Selected Cells.

Document & File Issues