RYG formula not working

jeckenrode42196

I have been researching formulas trying to find solutions without having to tap into the community and duplicate my question with others, however, I really am stuck.



Trying to create a formula for RYG based on planned finish date and status.

Here is what I wrote - unparsable is the error.  Are there supposed to be spaces between certain words?

=IF(AND(TODAY() > [Planned Finish]16, Status16 = "In Progress"), "Red", IF(AND(TODAY() > [Planned Finish]16, Status16 = "Not Started"), "Red", IF(AND(TODAY() > [Planned Finish]16, Status 16 = "Pending"), "Red", IF(OR(Status16 = "Completed", Status16 = "In Progress"), "Green", IF(Status16 = "Pending", "Yellow", IF(Status16 = "Not Started", "Gray"))))))

Mitch.Shaffer

You have a space in between "Status" and "16" in the third IF statement of the formula. Take out the space and it should work!

jeckenrode42196

It worked!!  Thank you!!

 

jeckenrode42196

Hi MItch - in my eagerness to fix the formula, I neglected to ask how to add in additional data from the status column.  I don't just have "not started", "in progress", and "completed"

I've added in "Under Review" (yellow), "Roadblocked" (red), and "On Hold" (yellow).

How do I add these in?  Please help.  Thanks!

Mitch.Shaffer

Do you need to have any of those other statuses related to the Planned Finish date like you have in the first sections of your formula?

Mitch.Shaffer

I took a stab at what you might want to see. Here is the updated formula:

=IF(AND(TODAY() > [Planned Finish]1, OR(Status1 = "Not Started", Status1 = "In Progress", Status1 = "Pending", Status1 = "Under Review", Status1 = "On Hold")), "Red", IF(Status1 = "Roadblocked", "Red", IF(OR(Status1 = "Pending", Status1 = "On Hold"), "Yellow", IF(Status1 = "Not Started", "Gray", "Green"))))

I've included a screenshot that shows how all the variations of finish dates and statuses will affect the end result RYG. Let me know if you see anything that needs to be changed!

jeckenrode42196

Thank you Mitch for the formula.  That works really well.  Where in the formula does it read if a task is completed?  Now working in this, i think it would be best to remove the RYG once a task is completed.  This way it doesn't look like the task is still in progress.  Or do you have another suggestion?  Thank you for your help.

Mitch.Shaffer

The completed section is the final "Green" at the end of the formula. That means if none of the previous conditions of the formula have been met, mark the cell green.

As for what the formula shows once a task has been completed, I think it's down to personal preference. Would you rather the cell be blank when a task has been completed?