Forum Discussion
help with an excel formula - pulling my hair out!
Can anyone please help create a formula for me, I have two date columns, start date and due date. I also have a % completion column.
I need to RAG rate the status based up on todays date, proximity to target completion date and % completion. These are the results I need returning.
If due date is greater than or equal to 2 weeks ahead of todays date, or if due date is less than or equal to 2 weeks ahead of todays date but % completion is greater than or equal to 50% then return 'G', if due date is less that 2 weeks ahead of todays date and % completion is less than 50% then return A, if due date is equal to todays date or prior to todays date and %completion is less than 100% then return 'R' - hope that makes sense.....help!!!
It gives G
and attached
Perhaps
=IF((B1<=TODAY())*(C1<1),"R",IF((B1<TODAY()+14)*(C1<0.5),"A",IF((B1>=TODAY()+14)+(B1<TODAY()+14)*(C1>0.5),"G","nothing")))
if due date is in B and % in C
Thanks so much for the reply, its nearly right, but I'm getting an A response where the work is 50% complete or more and there is less than 2 weeks to the due date, I'm also getting an A when the work is 100% complete but the due date has passed, I'd expect G for both of these, can you have another look please. Thanks so much.
It gives G
and attached
