Pls Help with RYGB Automation

Matthew Bertucci

I just can't get this to work properly...stuck..need help. Much appreciated.

PLS Help RYGB Status Automation based on dates

 

 

=IF([Identified as Optimization Project]@row = 0, "Not REO", IF(Status@row = "Red", "Not Approved", IF(Status@row = "Black", "Completed", IF([Task Count 2 Review]@row = 1, "Green", IF([Today's Date]@row >= [Gate 2 (Review) End Date]@row - 5, "Yellow", IF([Today's Date]@row <= [Gate 2 (Review) End Date]@row, "Blue", "Red"))))))

 

1.    If “Identified as Optimization Project is NOT checked= “Not REO”

2.    If Status@row= “Red” = “Not Approved”

3.    If Status@row= “Black” = “Completed”

4.    If the task count Review@row is checked= “Green” (any time this is checked it should be Green)

5.    If Today’s Date@row is between start date and start + 10 days is blue

6.    If today’s Date@row is in the last 5 days is yellow

7.    If its neither 4,5,6 then its red

I can get parts of the formula to work but not all together and I suspect is something with the order of operations??

Kelly Moore

Your priorization was exactly what I needed. Try this

=IF([Optimization Project]@row = 0, "Not REO", IF(Status@row = "Red", "Not Approved", IF(Status@row = "Black", "Completed", IF([Gate 1 (kick off)]@row = 1, "Green", IF(AND([Today's Date]@row < [Project Start Date]@row, ISDATE([Gate 1 (Kick off) End Date]@row), [Today's Date]@row < [Gate 1 (Kick off) End Date]@row - 5), "Blue", IF(AND([Today's Date]@row >= [Gate 1 (Kick off) End Date]@row - 5, [Today's Date]@row <= [Gate 1 (Kick off) End Date]@row), "Yellow", IF([Today's Date]@row > [Gate 1 (Kick off) End Date]@row, "Red")))))))

Kelly

Kelly Moore

Hey @Matthew Bertucci

If you're getting unexpected results, then yes, it's probably the order of your IFs. Remember that the formula moves from left to right until the FIRST true is found. If a statement is absolute (like your #4 that is Green anytime it is checked), it must be towards the beginning of the formula. That way it will trump any criteria that comes after it.

I wasn't clear if you wanted the numbered criteria above added to your existing formula (where the new criteria wasn't already included), or if you wanted the numbered criteria as a new formula. If it is the former, let me know and we can tweak the formula after understanding the priority of that criteria

=IF([task count 2 Review]@row = 1, "Green", IF(Status@row = "Red", "Not Approved", IF(Status@row = "Black", "Completed", IF([Optimization Project]@row = 0, IF(AND([Start Date]@row>=[Today's Date]@row, [Start Date]@row<=[Today's Date]@row+10), "Blue", "Not REO", IF(AND([Today's Date]@row >=TODAY(-5),[Today's Date]@row<TODAY()), "Yellow", "Red"))))))

Depending on criteria within your mentioned columns above, you may need additional criteria to refine your formula.

Will this formula work for you? If this formula provides unexpected results, please give feedback on exactly what conditions produced the unexpected results.

Kelly

Matthew Bertucci

Thank you so much Kelly for helping me. Your formula partially works but I had to rearrange it a bit. I've tried to explain in a more clear way what I'm looking for below.

1.      Identified as Optimization Project= 0 then “Not REO”

2.      Status is “Red” then “Not Approved”

3.      Status is “Black” then “Completed”

4.      If Gate 1 (kick off) =1 then “Green” EXCEPT if 1,2 or 3 are true they should over ride

Then comes the timing

5.      If Today’s Date = less than Project Start Date and 5 days prior to Gate 1 (kick off) End Date then “Blue”

6.      If Today’s Date is within 5 days of Gate 1 (Kick off) End Date then “Yellow”

7.      If Today’s Date has exceeded the Gate 1 (kick off) End Date then “Red”

I have 5 stage gates with each gate having different days allowed. So I will repeat this for the others but modify as needed. I’m getting hung up on the #5,6,7. I get #1-4 to work. I can get colors to work indepently but not together. I need to have the status turn yellow if to close the end date.

I hope this helps and thank you for looking at it!

Kelly Moore

Your priorization was exactly what I needed. Try this

=IF([Optimization Project]@row = 0, "Not REO", IF(Status@row = "Red", "Not Approved", IF(Status@row = "Black", "Completed", IF([Gate 1 (kick off)]@row = 1, "Green", IF(AND([Today's Date]@row < [Project Start Date]@row, ISDATE([Gate 1 (Kick off) End Date]@row), [Today's Date]@row < [Gate 1 (Kick off) End Date]@row - 5), "Blue", IF(AND([Today's Date]@row >= [Gate 1 (Kick off) End Date]@row - 5, [Today's Date]@row <= [Gate 1 (Kick off) End Date]@row), "Yellow", IF([Today's Date]@row > [Gate 1 (Kick off) End Date]@row, "Red")))))))

Kelly

Matthew Bertucci

That WORKED! SOOO happy! Thank you very much, now I just need to replicate this for the other 4 gate stages. Much Appreciated.

Matthew Bertucci

Sorry, one last one!

 

I got all the gates working but now I’m stuck on the last piece (overall gate health). Again its partially working but seems to get thrown off by the “yellow”. Do I need an “and” or “or” function?

My attempt at the formula.

=IF([Identified as Optimization Project]@row = 0, "Not REO", IF(Status@row = "Red", "Not Approved", IF(Status@row = "Black", "Completed", IF([Gate 1 (Kick Off) Status]@row = "Red", "Red", IF([Gate 1 (Kick Off) Status]@row = "Yellow", "Yellow", IF([Gate 1 (Kick Off) Status]@row = "Blue", "Blue", IF([Gate 2 (Review) Status]@row = "Red", "Red", IF([Gate 3 (Approval) Status]@row = "Red", "Red", IF([Gate 4 (Execution) Status]@row = "Red", "Red", "Green")))))))))

 

1.      Identified as Optimization Project= 0 then “Not REO”-works

2.      Status is “Red” then “Not Approved”-works

3.      Status is “Black” then “Completed”-works

4.      If any Gate (1-4) Status is “Red” then “Red”-works

5.      If any Gate (1-4) Status is “Yellow” then “Yellow”-sort of works (problem with 5-8)

6.      If all of the Gate (1-4) Status are “Blue” then “Blue”

7.      If all of the Gate (1-4) Status is either “Green” or “Blue then “Green”

8.      If any Gate (1-4) Status is “Yellow” then “Yellow”

Kelly Moore

Hey @Matthew Bertucci

I changed the approach as I prefer to use countifs when the number of permetations get above 3 or 4. This would give you the opportunity to add more gates, if needed. You would either change the last column if you inserted a column to the right of the last column. You wouldn't have to do anything if you inserted in between the existing first and last column. I also made an assumption that if none of the Gates had a ball in it but it didn't meet your first 3 IFs, you would want the Health to be blank. Without my assumption, the Health would show as Blue.

I was a bit confused what to do since #5 is the same as #8. If you want the Yellow last, move it to last in the formula.

=IF([Identified as Optimization Project]@row = 0, "Not REO", IF(Status@row = "Red", "Not Approved", IF(Status@row = "Black", "Completed", IF(COUNTIFS([Gate 1 (Kick Off) Status]@row:[Gate 4 (Execution) Status]@row, "Red") > 0, "Red", IF(COUNTIFS([Gate 1 (Kick Off) Status]@row:[Gate 4 (Execution) Status]@row, "Yellow") > 0, "Yellow", IF(AND(COUNT([Gate 1 (Kick Off) Status]@row:[Gate 4 (Execution) Status]@row) > 0, COUNTIFS([Gate 1 (Kick Off) Status]@row:[Gate 4 (Execution) Status]@row, <>"") = COUNTIFS([Gate 1 (Kick Off) Status]@row:[Gate 4 (Execution) Status]@row, AND(@cell <> "", @cell = "Blue"))), "Blue", IF(AND(COUNT([Gate 1 (Kick Off) Status]@row:[Gate 4 (Execution) Status]@row) > 0, COUNTIFS([Gate 1 (Kick Off) Status]@row:[Gate 4 (Execution) Status]@row, <>"") = COUNTIFS([Gate 1 (Kick Off) Status]@row:[Gate 4 (Execution) Status]@row, OR(@cell = "Blue", @cell = "Green"))), "Green")))))))

Will this work for you?

Kelly

Matthew Bertucci

That worked! Your assumption of the status ball being blank works well. Also, the countif is a new way for me to try. I appreciate all the help as I'm a bit over my skill level with this project. I guess that is how you learn by doing.

Sending you my appreciation!

Kelly Moore

It was my pleasure to help - and yes, this is definitely a learn by doing application. Don't hesitate to continue to reach out to the community to help accelerate your learning journey.

Matthew Bertucci

Hi Kelly,

Thanks again for the help with this formula.

I'm having an issue with the status ball not showing up. If today's date is less than the start date of project it works but past the start date its blank. If the box is checked it turns green.

It should be blue today and is blank.

I've tried messing with the "blue" statement and can't get it to work.

=IF([Identified as Optimization Project]@row = 0, "Not REO", IF(Status@row = "Red", "Not Approved", IF(Status@row = "Black", "Completed", IF(Status@row = "Green", "Completed", IF([Gate 1 (kick off)]@row = 1, "Green", IF(AND([Today's Date]@row < [Project Start Date]@row, ISDATE([Gate 1 (kick off) End Date]@row), [Today's Date]@row < [Gate 1 (kick off) End Date]@row - 5), "Blue", IF(AND([Today's Date]@row >= [Gate 1 (kick off) End Date]@row - 5, [Today's Date]@row <= [Gate 1 (kick off) End Date]@row), "Yellow", IF([Today's Date]@row > [Gate 1 (kick off) End Date]@row, "Red"))))))))

Matthew Bertucci

I think I figured it out. I had to add an OR statement and seems to be working now...pls disregard!

=IF([Identified as Optimization Project]@row = 0, "Not REO", IF(Status@row = "Red", "Not Approved", IF(Status@row = "Black", "Completed", IF(Status@row = "Green", "Completed", IF([Gate 1 (kick off)]@row = 1, "Green", IF(AND(OR([Today's Date]@row < [Project Start Date]@row, ISDATE([Gate 1 (kick off) End Date]@row), [Today's Date]@row > [Gate 1 (kick off) End Date]@row - 5)), "Blue", IF(AND([Today's Date]@row >= [Gate 1 (kick off) End Date]@row - 5, [Today's Date]@row <= [Gate 1 (kick off) End Date]@row), "Yellow", IF([Today's Date]@row > [Gate 1 (kick off) End Date]@row, "Red"))))))))

Kelly Moore

Great! So glad you got it to work out!

Matthew Bertucci

Trial and error got me there as I do not yet have a full grasp of the order of operations. Getting a little better each time. Thanks again for your support!