Red Yellow Green Gray
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.
Best Answer
-
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 | Workflow Consultant
info@evolytion.com
Answers
-
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 | Workflow Consultant
info@evolytion.com
-
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 :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!