If and Formula tracking date, progress with ryg status dashboard
To share back with the community here, I'd like to share some key formulas that I've been helped with by the community and extensive testing/fixing.
I have a sheet that tracks:
- progress for items based on user inputs of %age complete
- compared to the progress they should have completed so far (%age expected as of the current date compared to the start date/due dates)
- and gives you a detailed answer for what that progress means (good, delayed, off-track, completed, not started, etc.)
- and an associated formula that gives you a RYG(and Gray for not started) associated with the prior formula.
Detailed status formula:
=IF(AND([Calendar Due Date]@row < TODAY(), [Actual Progress]@row < 1), "NOT COMPLETE - OVERDUE", IF(AND([Actual Progress]@row = 1, [Date Completed]@row = ""), "ENTER COMPLETION DATE", IF(AND(([Calendar Due Date]@row - TODAY()) >= 0, [Actual Progress]@row <> 1, ([Planned Percent Complete Per Timeline]@row - [Actual Progress]@row) >= 0.25), "In Progress - Off Track", IF([Date Completed]@row - [Calendar Due Date]@row > 0, "Completed - Late", IF(AND([Planned Percent Complete Per Timeline]@row = 1, [Actual Progress]@row = 1), "Completed - On Time", IF(AND([Days Until Start]@row > 0, ([Actual Progress]@row - [Planned Percent Complete Per Timeline]@row) <= 0.1), "Not Started", IF(AND(([Planned Percent Complete Per Timeline]@row - [Actual Progress]@row) > 0.1, ([Planned Percent Complete Per Timeline]@row - [Actual Progress]@row) < 0.25), "In Progress - Delayed", "In Progress - Good")))))))
Dashboard (RYGG) formula:
=IF([Detailed Status]@row = "In Progress - Delayed", "Yellow", IF(OR([Detailed Status]@row = "In Progress - Off Track", [Detailed Status]@row = "NOT COMPLETE - OVERDUE"), "Red", IF(OR([Detailed Status]@row = "Completed - On Time", [Detailed Status]@row = "Completed - Late", [Detailed Status]@row = "In Progress - Good"), "Green", "Gray")))
Hope this helps some of you out with your formulas.
These may not be the most succinct formulas, but they work
Enjoy!
p.s. I know the "Not Started" only shows up for when it's prior to the start date, and that the other parts of the formula start showing results even if the user-entry %age is still 0% but the start date is already at/past today's date (this is ok for my purposes, since I want to know what the status is compared to my start dates, you may not like this, and if you find a way to do that in addition to this please post it for others use).
Comments
-
Well done! I've been planning to post similar topics myself. Thanks for sharing.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K 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
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!