RYG formula not working

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"))))))

Screen Shot 2018-04-09 at 2.33.57 PM.png

Find more posts tagged with

Formulas

Conditional Formatting

Project Management

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…