Formulas will be the death of me-
Firstly, formulas will be the death of me. My coworker wants to to do this-
If the overall plan is 25% complete and 25% through the duration = green
If the overall plan is 50% complete and 50% through the duration = green
If the overall plan is 75% complete and 75% through the duration = green
If the overall plan is 100% complete and 100% through the duration = green
If under the above bench mark but not 0% = yellow
If not at the above bench mark and 0% complete= red
I started this- but I think I am failing. The random 56 is 25% of the duration. I am not sure I like this guy enough to help any more (kidding)- thoughts?
=IF(AND(%[email protected]>24, [Due Date]@row - TODAY() > 56), "Green", IF(AND(%[email protected]>50, [Due Date]@row - TODAY() > 112), "Green", IF(AND(%[email protected]>75, [Due Date]@row - TODAY() > 168), "Green", IF(AND(%[email protected]<24, [Due Date]@row - TODAY() > 56), "Red", IF(AND(%[email protected]<50, [Due Date]@row - TODAY() > 112), "Red", IF(AND(%[email protected]<75, [Due Date]@row - TODAY() > 168), "Red",)))))))
Best Answer
-
Mike Wilday ✭✭✭✭✭✭Well. Let's see if I can help haha.
First off, I would make the percentage of days dynamic by creating a percentage column in the primary sheet that determines how far along the current task should be based on duration. It might be nice to see in a new column I am calling TimePercentage. Or I included a formula that does the math for you and embeds it into the color of the project.
For Calendar Days
=NETDAYS(TODAY(), [End date]@row) / NETDAYS([Start date]@row, [End date]@row)
This will calculate the current duration percentage of the project based on Calendar Days
For Working Days
=NETWORKDAYS(TODAY(), [End date]@row) / NETWORKDAYS([Start date]@row, [End date]@row)
Then your comparison formula can compare if the current project percentage matches the percentage of the duration.
=IF([% Complete]@row >= [TimePercentage]@row, "Green", IF([% Complete]@row > 0, "Yellow", "Red"))
OR you can embed the Time percentage in one single RGB symbol column like this...
For Calendar Days
=IF([% Complete]@row >= (NETDAYS(TODAY(), [End date]@row) / NETDAYS([Start date]@row, [End date]@row)) , "Green", IF([% Complete]@row > 0, "Yellow", "Red"))
For Working Days
=IF([% Complete]@row >= (NETWORKDAYS(TODAY(), [End date]@row) / NETWORKDAYS([Start date]@row, [End date]@row)), "Green", IF([% Complete]@row > 0, "Yellow", "Red"))
Here is a screenshot showing the working formulas...
Answers
-
Mike Wilday ✭✭✭✭✭✭
Formulas are fun for some. But this is a beast. What are the challenges this formula is presenting you? What questions do you have about it? Is it failing somewhere specific? Or are you just venting -- which is TOTALLY understandable. haha.
-
Thank you for saying this! Generally I use this go to formula - =IF(AND([email protected]= "In Progress", [End Date]@row - TODAY() > 10), "Green", IF(AND([email protected]= "In Progress", [End Date]@row - TODAY() < 10), "Red", IF([email protected]= "Complete", "Blue", IF(AND([email protected]= "Not Started", [End Date]@row - TODAY() > 30), "Green", IF(AND([email protected]= "Not Started", [End Date]@row - TODAY() < 10), "Red", IF(AND([email protected]= "Not Started", [End Date]@row < TODAY()), "Red", IF(AND([email protected]= "Not Started", [End Date]@row > TODAY() + 10, [End Date]@row < TODAY() + 30), "Yellow", IF(ISBLANK([Start Date]@row), "", ""))))))))
But this coworker wants something different where based on the duration of the entire project, the health changes at certain percentages and stages of time left to complete. My formula only got as far as the green health piece. He would like red health if outside certain parameters. So part of me is venting and part of me would like some superhero that has made formulas based on percentages + durations to give me a few tips. I am used to status/end date combos for health (see above).
-
Mike Wilday ✭✭✭✭✭✭
Well. Let's see if I can help haha.
First off, I would make the percentage of days dynamic by creating a percentage column in the primary sheet that determines how far along the current task should be based on duration. It might be nice to see in a new column I am calling TimePercentage. Or I included a formula that does the math for you and embeds it into the color of the project.
For Calendar Days
=NETDAYS(TODAY(), [End date]@row) / NETDAYS([Start date]@row, [End date]@row)
This will calculate the current duration percentage of the project based on Calendar Days
For Working Days
=NETWORKDAYS(TODAY(), [End date]@row) / NETWORKDAYS([Start date]@row, [End date]@row)
Then your comparison formula can compare if the current project percentage matches the percentage of the duration.
=IF([% Complete]@row >= [TimePercentage]@row, "Green", IF([% Complete]@row > 0, "Yellow", "Red"))
OR you can embed the Time percentage in one single RGB symbol column like this...
For Calendar Days
=IF([% Complete]@row >= (NETDAYS(TODAY(), [End date]@row) / NETDAYS([Start date]@row, [End date]@row)) , "Green", IF([% Complete]@row > 0, "Yellow", "Red"))
For Working Days
=IF([% Complete]@row >= (NETWORKDAYS(TODAY(), [End date]@row) / NETWORKDAYS([Start date]@row, [End date]@row)), "Green", IF([% Complete]@row > 0, "Yellow", "Red"))
Here is a screenshot showing the working formulas...
-
You are my freaking hero-
-
Mike Wilday ✭✭✭✭✭✭
Hahaha. Glad I could be of help. That was a fun puzzle to solve. :)
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/68354/\")<\/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":"
