How to Create Automated Red, Amber, Green Status Formulas in Excel
Question details
The user wants to assign a red, amber, or green (RAG) project status automatically based on the difference between planned and forecast completion dates.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking project progress by comparing planned and forecast/actual completion dates to highlight schedule delays.
- Observed behavior
- Needs a reliable method to calculate the delay in days and automatically apply corresponding red, amber, or green colors to indicate different status levels.
Ensure your planned and forecast dates are formatted as valid Excel dates before applying difference formulas or conditional formatting rules.
Calculate Delay and Apply Conditional Formatting Rules
Use a subtraction formula to find the delay in days, then apply conditional formatting rules to color-code the results as red, amber, or green.
To automate the project status, first calculate the exact delay by subtracting the planned date from the forecast date. Once the numeric difference is calculated, conditional formatting rules can automatically format the cell's background color based on the number of days delayed.
In a new 'Delay' column (e.g., Column E), enter a formula to subtract the planned date (Column C) from the forecast date (Column D), such as =D2-C2.
Select the cells containing the delay calculations. Go to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'.
Choose 'Format only cells that contain'. Set the rule description to 'Cell Value' 'less than or equal to' and enter 14. Click 'Format', choose a green fill color, and click OK.
Create another new rule for the same selection. Choose 'Cell Value' 'between', and enter 15 and 29. Format the fill color as amber/yellow and click OK.
Create a third rule for the selection. Choose 'Cell Value' 'greater than or equal to', and enter 30. Format the fill color as red and click OK.
Create RAG Status Trackers Easily with WPS Spreadsheet
WPS Spreadsheet provides intuitive conditional formatting tools to easily set up Red, Amber, and Green status alerts for your project timelines. It works flawlessly with your existing Excel schedules.
- 1. Open your schedule: Launch WPS Spreadsheet and open your existing project tracker workbook.
- 2. Select the target data: Highlight the column where you have calculated the delay between planned and forecast dates.
- 3. Access Conditional Formatting: Navigate to the Home tab and click the 'Conditional Formatting' icon.
- 4. Apply Highlighting Rules: Use 'Highlight Cells Rules' to quickly set up Less Than, Between, and Greater Than rules for your Green, Amber, and Red thresholds.
Frequently Asked Questions
Why is my conditional formatting applying the same color to the whole column?
This usually happens if you used absolute cell references (like $D$2) instead of relative references (like D2) in your custom conditional formatting formula. Update the rule to remove the dollar signs from the row numbers so the formula evaluates each row independently.
Can I use an IF formula instead of conditional formatting to show text like 'Red'?
Yes, you can use a nested IF formula to output the status text directly. For example: =IF(D2-C2>=30, "Red", IF(D2-C2>14, "Amber", "Green")). You can then optionally apply conditional formatting to color the text.
How do I ensure exactly 14 days shows as Green instead of Amber?
Ensure your conditional formatting rule or IF formula uses the 'less than or equal to' (<=) operator for the 14-day threshold, rather than strictly 'less than' (<). This resolves the ambiguity for the exact 14-day mark.
