IF(OR with RYGG

Tiera Mack

I would like to automate a RYGG column, assuming that my team is entering there own start and finish dates but due dates will be created by senior staff.

Yellow=In progress

Red=Overdue

Grey = Not started

Green=Completed

So IF start date is before due date, Yellow or if Finish date is before due date Green, etc. An assignment can be started late and finished on time but we want to track task weekly through reports.

=IF(OR([Start]7<[Due Date]7, ([Finish]7<=[Due Date]7, "Yellow", “Green”, IF(OR([Start]7>[Due Date]7, ([Finish]7<=[Due Date]7,"Red", “Green”, IF(OR(ISBLANK([Start]7), IF([Finish]7<=[Due Date]7, “Gray”, "Green"))))

I've tried a few different formulas.

Thank you,

Paul Newcome

Ok. IF statements work from left to right. So if the first IF is true, it will output that value. If it is not, it will move on to the next, so on and so forth.

Using that logic, we can start off by saying the IF the Finish@row IS a DATE, then output "Green"

=IF(ISDATE(Finish@row), "Green",

Then we move on to if the Start date ISBLANK. Blank dates will always be considered less than any input date, so we want to go ahead and cover that one...

=IF(ISDATE(Finish@row), "Green", IF(ISBLANK(Start@row), "Grey",

By using those two first then the Finish date MUST be blank and the Start date MUST be filled in to get to the next portion. That saves us from having to nest in AND functions to repeat criteria. So we just pick one. I'll do "Yellow".

=IF(ISDATE(Finish@row), "Green", IF(ISBLANK(Start@row), "Grey", IF(Start@row < [Due Date]@row, "Yellow",

Now we can save ourselves a little bit of typing. Since we already outlined all scenarios except for one, we can just say that anything that does NOT flag a true value from the previous IF statements would generate a "Red" and use one closing parenthesis for each IF statement to finish everything off.

=IF(ISDATE(Finish@row), "Green", IF(ISBLANK(Start@row), "Grey", IF(Start@row < [Due Date]@row, "Yellow", "Red")))

And there you have it. The formula now reads...

If Finish is a date, then "Green". If it is not a date and Start is blank, then "Grey". If Finish is not a date and Start is not blank (meaning there is a date entered) and Start is before the [Due Date], then "Yellow". The only other scenario left is that Finish is not a date, Start is not blank, and Start is not before the [Due Date] (meaning Start is past the [Due Date]) will generate a "Red".

Paul Newcome

Can you spell out all possibilities and their outcomes such as...

Green:

1. Finish Date is before Due Date

Yellow:

1. Start Date is before Due Date and Due Date is in the next n days.

Red:

1. Finish Date is blank and Due Date is in the past.

Tiera Mack

Paul,

Thank you for looking at this.

Yellow:

Start date is before due date

Red:

Start date is after due date

Grey:

Start date is blank

Green:

Finish date is before due date.

I was able to do this as a formula it read:

=IF(Start7 < [Due Date]7, "Yellow", IF(Start7 > [Due Date]7, "Red", IF(ISBLANK(Start7), "Gray", IF(Finish7 <= [Due Date]7, "Green")))).

I would like for Green to be automated in instances of when the start may be after the due date in Week 1 but in Week two the task is finished before the due date.

I was thinking it would be an IF(OR

My thinking is =IF(OR([Start]7<[Due Date]7, ([Finish]7<=[Due Date]7, "Yellow", “Green”

Sincerely,

Paul Newcome

I'm not sure I wuite understand this part...

"I would like for Green to be automated in instances of when the start may be after the due date in Week 1 but in Week two the task is finished before the due date."

If the start is after the due date, then how can the finish be before the due date?

Tiera Mack

For example,

Ahhh, I see my flawed logic, I want the balls to be green once the assignment is completed regardless of when it was started, it could have been started after the due date but once its completed I would like it to automatically turned green once a completion date is entered.

Sorry for the confusion and thank you for helping me think this through.

Sincerely,

Paul Newcome

Ok. IF statements work from left to right. So if the first IF is true, it will output that value. If it is not, it will move on to the next, so on and so forth.

Using that logic, we can start off by saying the IF the Finish@row IS a DATE, then output "Green"

=IF(ISDATE(Finish@row), "Green",

Then we move on to if the Start date ISBLANK. Blank dates will always be considered less than any input date, so we want to go ahead and cover that one...

=IF(ISDATE(Finish@row), "Green", IF(ISBLANK(Start@row), "Grey",

By using those two first then the Finish date MUST be blank and the Start date MUST be filled in to get to the next portion. That saves us from having to nest in AND functions to repeat criteria. So we just pick one. I'll do "Yellow".

=IF(ISDATE(Finish@row), "Green", IF(ISBLANK(Start@row), "Grey", IF(Start@row < [Due Date]@row, "Yellow",

Now we can save ourselves a little bit of typing. Since we already outlined all scenarios except for one, we can just say that anything that does NOT flag a true value from the previous IF statements would generate a "Red" and use one closing parenthesis for each IF statement to finish everything off.

=IF(ISDATE(Finish@row), "Green", IF(ISBLANK(Start@row), "Grey", IF(Start@row < [Due Date]@row, "Yellow", "Red")))

And there you have it. The formula now reads...

If Finish is a date, then "Green". If it is not a date and Start is blank, then "Grey". If Finish is not a date and Start is not blank (meaning there is a date entered) and Start is before the [Due Date], then "Yellow". The only other scenario left is that Finish is not a date, Start is not blank, and Start is not before the [Due Date] (meaning Start is past the [Due Date]) will generate a "Red".

Tiera Mack

Thank you @Paul Newcome

Paul Newcome

Happy to help! 👍️