How to Create a Formula for RAG Ratings on Delayed Tasks in Microsoft Project
Question details
The user needs to classify project schedule performance into Red, Amber, Green, or Complete categories based on baseline and scheduled finish dates.
- Product
- Microsoft Project
- Device & OS
- not provided
- Scenario
- Tracking project schedule performance and visually highlighting delayed tasks using a custom nested formula.
- Observed behavior
- Requires mapping the date differences between scheduled finish and baseline finish into a visual RAG status, while handling exceptions like 100% completion and specific gap days.
Ensure your project has a saved baseline and that task progress has been updated so the formula can accurately compare scheduled finish dates against baseline finish dates.
Create a Custom RAG Formula Field and Apply Graphical Indicators
Use a custom text field with a nested IIf formula to calculate the task delay and assign a RAG (Red, Amber, Green) status.
When a scheduled finish date slips past the baseline finish date, a nested IIf formula can categorize the severity of the delay. Before finalizing the formula, ensure you account for edge cases such as tasks without baselines, gap periods (e.g., 11–14 days), and whether you are calculating working days versus calendar days.
Go to the Project tab on the ribbon and click on Custom Fields.
Select Task and Text from the Type dropdown. Click Rename to name the field 'RAG Status'.
Under the Custom attributes section, select Formula. Enter your nested IIf formula. For example: IIf([% Complete]=100,"Complete",IIf(DateDiff("d",[Baseline Finish],[Finish])>=15,"Red",IIf(DateDiff("d",[Baseline Finish],[Finish])>0 And DateDiff("d",[Baseline Finish],[Finish])<=10,"Amber","Green"))).
Click OK to save the formula. Next, click the Graphical Indicators button in the Custom Fields dialog and map your text values ('Red', 'Amber', 'Green', 'Complete') to colored icons.
Manage Project Schedules Effortlessly with WPS Spreadsheet
While Microsoft Project offers specialized scheduling tools, WPS Spreadsheet provides a highly flexible, free alternative for managing project timelines. Easily track task delays, handle complex IF formulas, and implement visual RAG statuses using built-in conditional formatting without the steep learning curve.
- 1. Set Up Your Data Columns: Open WPS Spreadsheet and create columns for 'Baseline Finish', 'Actual Finish', and 'Delay Days'.
- 2. Calculate Delay Days: In the 'Delay Days' column, subtract the baseline date from the actual finish date (e.g., =B2-A2).
- 3. Apply an IF Formula: Create a 'Status' column and use a standard nested IF formula to assign text, such as =IF(C2>=15,"Red",IF(C2>0,"Amber","Green")).
- 4. Add Conditional Formatting: Select the 'Status' column, go to Home > Conditional Formatting, and set rules to highlight cells containing 'Red', 'Amber', or 'Green' with their respective colors.
Frequently Asked Questions
Why is my RAG formula returning an error for certain tasks?
This usually happens when a task lacks a saved baseline finish date, causing the DateDiff function to fail. You can add logic to your formula, such as IIf([Baseline Finish]=ProjDateValue("NA"), "No Baseline", ...), to handle missing baselines gracefully.
Can I calculate working days instead of calendar days in Microsoft Project formulas?
Yes. Instead of using DateDiff("d", ...), which calculates standard calendar days, you can use the ProjDateDiff([Baseline Finish],[Finish]) function to calculate the difference based strictly on the project's working calendar.
What happens if a task is 100% complete but was finished late?
If your formula evaluates [% Complete]=100 as the very first condition, it will return 'Complete' and ignore the delay entirely. If you want a completed task to still show a Red or Amber status indicating it finished late, you must adjust the order of your nested IIf statements to evaluate the delay before checking the completion percentage.
