# Project Health Automation on % Complete and Start/Finish Dates

✭✭

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 🤪

• ✭✭✭✭✭✭

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

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

• ✭✭

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

• ✭✭
• ✭✭✭✭✭✭

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?

• ✭✭

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)

• ✭✭✭✭✭✭

Hi @Cyndi

I hope you're well and safe!

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭

@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 -

• ✭✭✭✭✭✭

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)

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭

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.

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!