How to Set Excel Conditional Formatting for Review Dates (4, 8, 12 Months)
Question details
The user wants to set up conditional formatting to color-code review dates at 4, 8, and 12 months. The colors should indicate if a review is current (green), due within one month (amber), or overdue (red), and the formatting must clear when a completion date is entered.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking employee reviews, project milestones, or compliance deadlines using visual color-coded alerts that automatically update based on the current date and completion status.
- Observed behavior
- Setting up rules using EDATE and TODAY functions to dynamically apply green, amber, and red backgrounds while a completion cell remains blank.
Ensure your spreadsheet has dedicated columns for the Start Date, the Target Review Date, and the Completion Date, and verify that these columns contain valid date values rather than plain text.
Apply Formula-Based Conditional Formatting with EDATE and TODAY
Create custom formula rules to check the target date against the current date, calculate milestones, and verify if the completion cell is empty.
To achieve a dynamic traffic-light system (Red, Amber, Green), you need to create three separate conditional formatting rules using the AND function. This allows Excel to check two conditions simultaneously: whether the date criteria is met, and whether the completion cell is still blank.
Highlight the cells or columns containing the review milestone dates where you want the color formatting to apply.
Go to the Home tab, click 'Conditional Formatting', and select 'New Rule'. Choose 'Use a formula to determine which cells to format'. Enter the formula =AND(E4<TODAY(),F4="") (assuming E4 is the milestone date and F4 is the completion date). Click 'Format', select a red fill color, and click OK.
Click 'New Rule' again. Use the formula =AND(EDATE(E4,-1)<TODAY(),F4="") to highlight dates due within one month. Format this rule with an amber or yellow fill color.
Add a third 'New Rule' using the formula =AND(EDATE(E4,-1)>=TODAY(),F4=""). Format this rule with a green fill color to indicate the review is on track.
If you are calculating the 4, 8, or 12-month dates dynamically from a start cell (e.g., A4), you can replace E4 in the formulas with EDATE(A4, 4), EDATE(A4, 8), or EDATE(A4, 12).
Easily Apply Conditional Formatting in WPS Spreadsheet
WPS Spreadsheet offers a powerful and intuitive Conditional Formatting tool fully compatible with Microsoft Excel formulas like EDATE and TODAY. Easily track your project deadlines and review milestones for free.
- 1. Open your file: Launch WPS Spreadsheet and open your tracker document containing the start and review dates.
- 2. Access Conditional Formatting: Highlight your target cells, navigate to the Home tab, and click the 'Conditional Formatting' icon.
- 3. Create custom rules: Select 'New Rule', choose 'Use a formula to determine which cells to format', and input your AND/EDATE formulas exactly as you would in Excel.
- 4. Manage rule hierarchy: Use the 'Manage Rules' option to easily drag and prioritize your Red, Amber, and Green indicators.
Frequently Asked Questions
Why are my conditional formatting formulas not working for blank cells?
Blank cells might be interpreted as zeros (January 0, 1900) by Excel. Use the ="" criteria, like F4="" or ISBLANK(F4), inside an AND function to explicitly check that the completion date is missing.
How do I calculate exactly 4, 8, or 12 months from a start date?
Use the EDATE function. For example, =EDATE(A2, 4) adds exactly 4 months to the date in cell A2. You can then wrap this EDATE formula inside your conditional formatting rules.
Can I apply the color to the entire row instead of just the date cell?
Yes. Select the entire range of data you want to format. When writing your formula, lock the column reference with a dollar sign (e.g., =$E4<TODAY()). This ensures the whole row is formatted based on the value in column E.
What happens to the color if I enter a completion date?
Because your formulas include the condition F4="" (where F4 is the completion cell), the moment you type anything into F4, the condition becomes false. The color formatting will clear automatically, returning the cell to its default state.
