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 🤪
Answers
-
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
-
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!
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!
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!