Formula based on Completion Date, Status and symbols

CorrinaD

Hi, I am trying to write a formula saying:

If blank then blank

If Status column has "Full" symbol Progression to Completed Date column is "Green"

Or if Completion Date is greater than today is "Red"

Or if Completion Date is less than 7 days is "Yellow" otherwise is "Green"

So far I have this, that is working well for altering my Progression to Completed Date symbols, but unable to return the if Status is "Full" will be "Green" despite date. And blank.

=IF([Completion Date]1 < TODAY(), "Red", IF(AND([Completion Date]1 >= TODAY(), [Completion Date]1 < TODAY(+7)), "Yellow", "Green", IF(AND(Status1 = "Full", "Green"))))

Thanks heaps for any assistance.

CorrinaD

Cracked it, just took a little more time:

=IF(Status1 = "", "", IF(Status1 = "Full", "Green", IF([Completion Date]1 < TODAY(), "Red", IF(AND([Completion Date]1 >= TODAY(), [Completion Date]1 < TODAY(+7)), "Yellow", "Green"))))

CorrinaD

Cracked it, just took a little more time:

=IF(Status1 = "", "", IF(Status1 = "Full", "Green", IF([Completion Date]1 < TODAY(), "Red", IF(AND([Completion Date]1 >= TODAY(), [Completion Date]1 < TODAY(+7)), "Yellow", "Green"))))

Genevieve P.

Hey @CorrinaD

Thanks for posting your solution! I'm glad you were able to get a formula that works for you.

Cheers,

Genevieve