RYG Formula and Status Question - almost there
Hi! Below is what I'm trying to solve for:
If the status says Complete, then grey.
If the status says At Risk, or In Progress then calculate the RYG status based on the parameters below.
If the Target End date is 15 or more days from today, it's green.
If the Target End date is 4-14 days from today, it's yellow.
If the Target end date is equal to today, less than 3 days from today or in the past it's red.
I've tried a few different formulas but keep getting an error about incorrect argument set. Please help!
Answers
-
What's the formula you're using? I know parenthesis can be tricky buggers, could it be you're missing one?
-
I'm using:
=IF(Status@row = "Complete", "Gray", IF(OR(Status@row = "At Risk", Status@row = "In Progress", [Target End Date]@row >= TODAY(15), "Green", IF([Target End Date]@row < TODAY(3), "Red", "Yellow")))))
-
Hi @johnna.young,
I think this will do what you're after:
=IF(Status@row = "Complete", "Gray", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(15)), "Green", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(4)), "Yellow", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row <= TODAY(3)), "Red", ""))))
Sample:
The only thing it doesn't cope too well with is if you have an At Risk/In Progress status with no target end date - these will show up as Red.
Hope this helps, if you've any problems/questions then just post! 🙂
-
This worked! Thank you so much!! For my own understanding. Can you explain why both the "And" and "Or" functions were used? @Nick Korna
-
The OR is used because you want 1 of 2 options from the Status column.
The ANDs are used because you want both the Status column OR and the date range check simultaneously.
