Project Health Automation on % Complete and Start/Finish Dates

Cyndi

For some reason (Probably user error) I do not receive responses back when I reply to an already created item - so please let me know what I can do better next time. I have this formula very close thanks to all the wonderful advise you have provided still a nogo. Standard Project plan looking for Health -

IF([% Complete]@row = 1, "Green", IF(TODAY() > Finish@row, "Red", IF(TODAY(10) >= Finish@row, IF([% Complete]@row < 0.75, "Yellow", "Blue")))) -- almost gets me there

Then I thought I had it figured out and started getting #invalid data type for future dates when I tried to add in the last bullet below; What I want at the end of the day:

Green: If "% Complete" is 100% (and this one works [% Complete]@row = 1, "Green")

Red: if "% Complete" is <100% when "finish" is (This one works: IF(TODAY() > Finish@row, "Red")

Yellow : If "Finish" is within 10 days and we are <10 days out from the "Finish" date (This one works IF(TODAY(10)>="Finsh@row""% Complete" is <.75 )

What doesn't work is

I still want "Green" to display if the "Start Date" is in the future.

Also I want "Green" if I am still within the Start Date/Finish Date period - (UNLESS: the finish meets the rule above that actually does work)

I think that covers me or at least fixes most of it

Thanks 🤪

Kelly Moore

Hey @Cyndi

If I understand correctly, the status would be Green if the Finish date was greater than 10 days out and the %complete is greater than or equal to 75%.

IF(AND([Finish Date]@row>=TODAY(10), [% Complete]>=0.75), "Green")

I would put this ahead of your Yellow/Blue IFs

IF([% Complete]@row = 1, "Green", IF(TODAY() > Finish@row, "Red", IF(AND([Finish Date]@row>=TODAY(10), [% Complete]>=0.75), "Green", IF(TODAY(10) >= Finish@row, IF([% Complete]@row < 0.75, "Yellow", "Blue")))))

I don't think you still need the IF immediately following the inserted Green but since you said that part was working I left it alone.

Does this get you what you need?

Kelly

Cyndi

Boo:( I get #UNPARSEABLE - is there a way to save as a Template and share?

Cyndi

Kelly Moore

Hey @Cyndi

It looks like there are a couple of inadvertent slashes in your formula following the added Green. You can delete those to up to the orange parenthesis in front of TODAY(10).

Does that clear the error?

Cyndi

Unfortunately no - here is the updated formula. It's odd because the original one works, its just when we add in that additional direction. So sorry for bothering you - I'll keep playing with it - just driving me crazy

Here is the one again that works (well works but doesn't capture all the criteria)

Andrée Starå

Hi @Cyndi

I hope you're well and safe!

To add to Kelly's excellent advice/answer.

Try something like this. (@row was missing)

IF([% Complete]@row = 1, "Green", IF(TODAY() > Finish@row, "Red", IF(AND([Finish Date]@row>=TODAY(10), [% Complete]@row>=0.75), "Green", IF(TODAY(10) >= Finish@row, IF([% Complete]@row < 0.75, "Yellow", "Blue")

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Cyndi

@Andrée Starå I love this group ;) This is so much fun, I'm relatively new to advanced functions, so I'm super excited and yes we are well here in Dallas Texas but I think I have really stumped everyone on this. LOL. Im' sure its something very simple that's still out there - because I am still not able to pull a valid answer. I wish there were a way we could upload a template for share on here -

Andrée Starå

@Cyndi

Haha! Glad to hear you're well!

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Cyndi

Yey - okay really what I am trying to accomplish is to evaluate the health of my plan. I shared the sheet and I've added another column for testing.

For items that are complete I want them green -

But for items that have not started yet (start date <= Today) , those too should be green or blue (I don't really care about their color as much for these guys- so they can be green or blue, whatever)

For items that are past due - (Today > the finish date) and the % Column is not = 100 - then they should be Red.

Here comes the tricky part and ... I think this is what is creating the hicccup -

Lets say the task is in process and is good to go....they should still be in Green, BUT if we get within 10 days of the due date and the % Complete is <=75, then they are YELLOW

The last bullet actually works really well.

Andrée Starå

@Cyndi

It's fixed!

There was a reference to a column that didn't exist with that name.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!