Could anyone help us create the proper formula? Parent/Children Health based off Due Dates
We are trying to utilize Smartsheet to list out, follow, and forecast our project schedules. The idea is that each Milestone of the Project is the Parent and the steps within the milestone are the children. We are having issues with the parents correctly representing an issue/delay within its children. Right now the formula we are using is:
=IF([Start Date]39 > TODAY(), "Blue", IF(AND([% Complete]39 < 1, [End Date]39 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green")))
this was taken from row 39 of a sheet (would love a universal @ row Formula)
Essentially we want the health to reflect the steps current status in this way
Red means Overdue (Due Date>Today )
Yellow means Status is In Progress AND Due Date upcoming (Due Date<14 Days away)
Green means Status is in Progress, Start date has passed, but has not within 14 days of being Due
Blue means Status is In Queue
If anyone can assist with this vision it would be much appreciated!
Answers
-
If you are using %complete, you can use something like this:
=IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(3), "Yellow", "Green")))
Does that help?
-
Thank you for your suggestion Hank!
We are more looking to make a formula which reflects something like this: (for some reason this is Unparseable)
=IF([Start Date]@row > TODAY(), "blue"), IF([% Complete]@row > 1, "yellow"), IF(AND([% Complete]@row < 100, ([End Date]@row > TODAY()), "Red")
essentially combining the four formulas below into one formula
=IF([Start Date]@row > TODAY(), "blue")
=IF([% Complete]@row >= 1, "yellow")
=IF(AND([% Complete]@row < 100, ([End Date]@row > TODAY()), "Red")
=IF(Status@row = "Complete", "Green")
-
What if a task is less than 14 days away? You could have a task that is not yet started but the due date is within 14 days?
Based on your most recent post, I would use something along the lines of...
=IF(Status@row = "Complete", "Green", IF([Start Date]@row> TODAY(), "Blue", IF([End Date]@row< TODAY(), "Red", "Yellow")))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!