Create an Excel RAG Status Formula Based on Date Differences
Question details
The user needs a formula to generate a Red, Amber, or Green (RAG) status based on the number of days between start and end dates, specifically requiring blank end dates to return RED without being overridden.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking project timelines or task deadlines where a visual RAG status must update automatically based on duration or missing completion dates.
- Observed behavior
- The current setup allows the 'under 30 days' (GREEN) logic to take precedence, preventing the formula from correctly displaying RED when the end date cell is completely blank.
Ensure your start and end dates are formatted as valid dates rather than text, and identify the exact column letters you are referencing before constructing the formula.
Use a Priority-Based Nested IF Formula
Structure your nested IF formula to evaluate the blank end date condition first, preventing subsequent mathematical conditions from overriding it.
In Excel, an IF function evaluates conditions in the exact order they are written. When calculating date differences, a blank cell can sometimes be interpreted as zero, which inadvertently triggers 'less than 30 days' rules. By placing the blank check at the very beginning of the formula, it takes priority.
Click on the cell in your status column where you want the RAG text (RED, AMBER, GREEN) to appear.
Type the formula: =IF(D2="","RED",IF(D2-C2<30,"GREEN",IF(D2-C2<60,"AMBER","RED"))). In this example, C2 is the Start Date and D2 is the End Date.
Press Enter to save the formula. Click the cell again, grab the fill handle in the bottom-right corner, and drag it down to apply the logic to all relevant rows.
Apply Conditional Formatting for Visual Colors
Use Conditional Formatting to automatically colorize the cells based on the text output of your RAG formula.
Track Project Statuses Effortlessly with WPS Spreadsheet
WPS Office provides seamless support for advanced formulas like nested IFs and priority-based Conditional Formatting, making it incredibly easy to set up dynamic RAG tracking for any project schedule.
- 1. Open your tracker: Launch WPS Spreadsheet and open your project timeline document.
- 2. Input the RAG formula: Enter the nested IF formula checking for blank dates first into your status column.
- 3. Apply visual formatting: Use the Conditional Formatting tool found on the Home tab to automatically turn the output text into vibrant red, amber, and green cells.
Frequently Asked Questions
Why does my formula return a #VALUE! error?
This usually occurs if the start or end date cells contain text instead of properly formatted dates. Ensure both columns are set to the 'Date' format and contain valid date entries.
Can I customize the RAG formula to use different day thresholds?
Yes, you can adjust the numerical values inside the formula. For example, change <30 to <15 if you want the GREEN status to apply only when the date difference is under 15 days.
What does the 'Stop If True' checkbox do in Conditional Formatting?
It instructs the spreadsheet program to stop evaluating any subsequent formatting rules once the current rule's condition is met. This is vital for preventing rule conflicts, such as an empty cell unintentionally triggering a 'less than 30' numerical rule.
How do I calculate working days only for my RAG status?
Instead of simply subtracting the dates (D2-C2), use the NETWORKDAYS(C2, D2) function inside your IF statement to exclude weekends from your 30-day and 60-day thresholds.
