Red Yellow Green Gray

Zoe Macfarlane

Hi Experts :),

I need help with my formula.

Principles I am trying to achieve are:

• if Min Variance column is blank AND the Minister Due Date is past today by less than 2 days, OR the Min Variance@row is Less than 2, then flag Yellow.
• If the Min Variance column is blank, AND the Minister Due Date is past today by more than 2 days OR the Min Variance@row is greater than 2, then flag Red
• If the Min Variance column is blank AND the Minister due date has not yet been reached OR the Min Variance @row is less than or equal to 0, then flag Green
• If no conditions are met flag gray.

I am very stuck on the brackets. But there may be a simpler way?

= IF(ISBLANK(AND([Min Variance]@row), [Minister Due Date]@row > TODAY(3), OR[Min Variance]@row >= 2)), "Red", IF(ISBLANK(AND(OR([Min Variance]@row), [Minister Due Date]@row > TODAY, [Min Variance]@row < 2))),"Yellow", IF(ISBLANK(AND([Min Variance]@row), [Min Due Date]@row <=0, "Green", "Gray"))

Really appreciate your help.

Mathieu PERSICO

Hi Zoe,

Try this formula:

=IF(OR(AND(ISBLANK([Min Variance]@row), TODAY(-2) < [Minister Due Date]@row, [Minister Due Date]@row < TODAY()), AND(NOT(ISBLANK([Min Variance]@row)), [Min Variance]@row < 2)), "Yellow", IF(OR(AND(ISBLANK([Min Variance]@row), [Minister Due Date]@row < TODAY(-2)), AND(NOT(ISBLANK([Min Variance]@row)), [Min Variance]@row > 2)), "Red", IF(OR(AND(ISBLANK([Min Variance]@row), TODAY() < [Minister Due Date]@row), [Min Variance]@row < 0), "Green", "Gray")))

Is it what you are asking for?

Mathieu PERSICO

Hi Zoe,

Try this formula:

=IF(OR(AND(ISBLANK([Min Variance]@row), TODAY(-2) < [Minister Due Date]@row, [Minister Due Date]@row < TODAY()), AND(NOT(ISBLANK([Min Variance]@row)), [Min Variance]@row < 2)), "Yellow", IF(OR(AND(ISBLANK([Min Variance]@row), [Minister Due Date]@row < TODAY(-2)), AND(NOT(ISBLANK([Min Variance]@row)), [Min Variance]@row > 2)), "Red", IF(OR(AND(ISBLANK([Min Variance]@row), TODAY() < [Minister Due Date]@row), [Min Variance]@row < 0), "Green", "Gray")))

Is it what you are asking for?

Zoe Macfarlane

Thank you so much. This helped a lot.

I made a small change to your above formula (less than and equal to) and it is working a treat.

=IF(OR(AND(ISBLANK([Min Variance]@row), TODAY() <= [Minister Due Date]@row), [Min Variance]@row <= 0), "Green", IF(OR(AND(ISBLANK([Min Variance]@row), TODAY() <= [Minister Due Date]@row), [Min Variance]@row <= 2), "Yellow", IF(OR(AND(ISBLANK([Min Variance]@row), TODAY() <= [Minister Due Date]@row), [Min Variance]@row >= 2), "Red", "Gray")))

I just need to make this formula work. I subtract 1 to account for when the dates are the same I dont want it to record as being late. But when it the task is delivered early it subtracts -1 to a negative number making it look an extra early.

=IFERROR(NETWORKDAYS([Minister Due Date]@row, [Minister Actual Date]@row) - 1, "")

So i need a formula that says networkdays between two dates but don't count the either date as a day I think.

hahaha formula fun.

Thank you for your help :)