RYG Formula and Status Question - almost there

johnna.young

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!

JSanita

What's the formula you're using? I know parenthesis can be tricky buggers, could it be you're missing one?

johnna.young

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")))))

Nick Korna

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! 🙂

johnna.young

This worked! Thank you so much!! For my own understanding. Can you explain why both the "And" and "Or" functions were used? @Nick Korna

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.

johnna.young

@Nick Korna Hello. I need to update this formula to include "On Hold" but i'm not sure where it fits. Currently it is:

If the status says Complete, then blue.

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.

=IF(Status@row = "Complete", "Blue", 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", ""))))

I would like to add "If the status is "On Hold", then it's blank. Where would I add that in this formula?

Nick Korna

If it is just the status being "On Hold", you can slot it in anywhere along the nested chain.

Having said that, what result are you currently getting for this as if non of the other conditions apply (which all require a status of one of Complete/At Risk/In Progress) then you should be getting a blank result.

If, for some reason it isn't working like this, you can amend to something like:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "On Hold","", 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", "")))))

You just need to add the extra status IF and an extra bracket on the end.

Hope this helps out in either case! 🙂