RYG based on Status and End Date

hjohnson

Hi there,

I've been combing through the posts and not finding exactly what I'm looking for...so reaching out to the experts for some help. Below is what I was hoping to accomplish.

Any help would be much appreciated.

Thanks,

If Status is Complete then ball color should be Green

If Status is Not Started or In Progress and End Date is greater than Today then the ball color should be Green

If Status is Not Started or In Progress and End Date is within the next 14 days then the ball color should be Yellow

If Status is Not Started or In Progress and End Date is within the next 5 days then the ball color should be Red and Status should change to At Risk – Coming Due (not sure if I need to have a separate formula on the Status column for the changing of the Status to work)

If Status is Not Started or In Progress and End Date is less than Today then the ball color should be Red and the Status should change to At Risk – Coming Due (not sure if I need to have a separate formula on the Status column for the changing of the Status to work)

=IF(Status@row = "Complete", "Green", IF(AND(Status@row = "In Progress", [Start Date]@row > TODAY(), [End Date]@row > TODAY()), "Green", IF(AND(Status@row = "In Progress", [Start Date]@row < TODAY(), [End Date]@row > TODAY()), "Green", IF(AND(Status@row = "In Progress", [Start Date]@row < TODAY(), [End Date]@row < TODAY(14)), "Yellow", IF(AND(Status@row = "In Progress", [Start Date]@row < TODAY(), [End Date]@row < TODAY(5)), "Red", IF(AND(Status@row = "Not Started", [Start Date]@row > TODAY(), [End Date]@row > TODAY()), "Green", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(), [End Date]@row > TODAY()), "Green", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(), [End Date]@row < TODAY(14)), "Yellow", IF(AND(Status@row = "Not Started", [Start Date]@row < TODAY(), [End Date]@row < TODAY(5)), "Red")))))))))

hjohnson

@Genevieve P. - You helped with me a different formula question...any chance you would be available to assist with this one? Thanks!

Cheryl Collins

Hi

I have a very similar question. I need the following:

If [% Complete] =< Half and the [Deadline Date] is >TODAY + 21 days, RAG = "Green"

If [% Complete] =< Half and the [Deadline Date] is >TODAY + 14 days, RAG = "Amber"

If [% Complete] =< Three Quarter and the [Deadline Date] is >TODAY + 10 days, RAG = "Red"

This is what I have so far:

=IF([Deadline Date]@row >= TODAY(+21), "Green", IF((AND([% Complete]@row = "Empty", [% Complete]@row = "Quarter", [% Complete]@row = "Half", [Deadline Date]@row <= TODAY(+14)), "Yellow", "Red")))

I get a #UNPARESABLE though

@Paul Newcome @Andrée Starå are either of you able to help us as you are amazing with formulas! 😍

TIA

Cheryl

Just resolved it myself:

=IF([Deadline Date]@row > TODAY(+21), "Green", IF((AND([% Complete]@row = "Empty", [% Complete]@row = "Quarter", [% Complete]@row = "Half", [Deadline Date]@row < TODAY(+14))), "Yellow", "Red")) 🙊

Paul Newcome

@hjohnson You will need a separate formula for the status column, but this should work in your RYG column:

=IF(Status@row = "Complete", "Green", IF([End Date]@row <= TODAY(5), "Red", IF([End Date]@row <= TODAY(14), "Yellow", "Green")))

@Cheryl Collins Did you test out your formula? I ask because it seems as if the portion in bold shouldn't be working as expected. Basically you are saying if the [% Complete] is "Empty" AND "Quarter" AND "Half" all at the same time.

=IF([Deadline Date]@row > TODAY(+21), "Green", IF((AND([% Complete]@row = "Empty", [% Complete]@row = "Quarter", [% Complete]@row = "Half", [Deadline Date]@row < TODAY(+14))), "Yellow", "Red"))

I would imagine you needed an OR statement tucked in there?

=IF([Deadline Date]@row > TODAY(+21), "Green", IF(AND(OR([% Complete]@row = "Empty", [% Complete]@row = "Quarter", [% Complete]@row = "Half"), [Deadline Date]@row < TODAY(+14)), "Yellow", "Red"))