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
-
Chris McKay ✭✭✭✭✭✭Well done! I've been planning to post similar topics myself. Thanks for sharing.
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/22921/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
