To track status of a project or tasks, RAG (Red, Amber, Green) report is commonly used. Given below is a sample RAG report as on 04-Jun-09
The following logic is used for creating the report
For Open tasks:
- Red: SCD – today()+1 is -ve
- Amber: SCD – today()+1 is 0
- Green: SCD – today()+1 is +ve
For Closed tasks:
- Red: SCD – ACD+1 is -ve
- Amber: SCD – ACD+1 is 0
- Green: SCD – ACD+1 is +ve
SCD = Scheduled Completion Date
ACD = Actual Completion Date
The following formulas are used
C2=CHOOSE(WEEKDAY(B2),”Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”)
H2=IF(F2=”Open”,CHOOSE(2+SIGN(E2-TODAY()+1),”Red”,”Amber”,”Green”),CHOOSE(2+SIGN(E2-G2+1),”Red”,”Amber”,”Green”))
Column H is conditionally formatted to colour the cells – Red, Amber or Green
Choose function Uses index_num to return a value from the list of value arguments has the syntax CHOOSE(index_num,value1,value2,…)
Sign function returns (0,+1,-1) depending on whether the input is 0,+ve or -ve
You could change the formula depending on the logic you want to use for RAG.
Have fun 🙂