Checkmark Parent if any Children Due Date is in the next 7 Days
Hello!
Stuck testing out formulas to add aColumn [Children Due Soon] that checkmarks if aparent row [Task Name]has any children task isdue [Due] in the next 7 days.
I've been trying to edit the famous RGB formula for this, but still not very familiar with formulas.
Thanks!
Best Answer
-
Paul Newcome ✭✭✭✭✭✭@MaricarmenActually I think you may have it right. I was looking at dates in the PAST 7 days whereas you want NEXT 7 days. Your COUNTIFS is actually the correct one. That was my mistake.
Here...
=IF(COUNTIFS(CHILDREN(Due@row), AND(@cell <= TODAY(+7), @cell >= TODAY())) > 0, 1)
Answers
-
Paul Newcome ✭✭✭✭✭✭
-
Maricarmen Vargas ✭✭✭✭
Hi Paul, its a regular Date Select. I just changed the format of it :)
-
Maricarmen Vargas ✭✭✭✭
First time posting in the community, think I wasn't supposed to reject your comment@Paul Newcome.
Hope that didn't do anything negative!
-
Paul Newcome ✭✭✭✭✭✭
Excellent. That definitely simplifies things quite a bit!
What we will do is count how many children rows have dates that are within the specified range. If that count is greater than 0, we will check the box.
To count:
=COUNTIFS(CHILDREN(Due@row), @cell >= TODAY(-7))
Add in the IF:
=IF(COUNTIFS(CHILDREN(Due@row), @cell >= TODAY(-7)) > 0, 1)
-
Paul Newcome ✭✭✭✭✭✭
I believe it just marks the post as "Unanswered". This is a relatively new format and feature to the Community, so I am not sure.
If my response solves your problem, you can mark it at the bottom of the post as being helpful to flag it as the "Accepted Answer". This will let others in the community with a similar problem know that a solution can be found here.
-
Andrée Starå ✭✭✭✭✭✭@Paul NewcomeYes, you're correct. It only selects it as not being an accepted answer.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Paul Newcome ✭✭✭✭✭✭
-
Andrée Starå ✭✭✭✭✭✭
@Paul NewcomeYou're welcome!
I've talked with the community team about updating the wording because it can feel a little negative.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Maricarmen Vargas ✭✭✭✭
Not sure if I did something incorrectly, but the checkmark is still applied even when no children sub tasks have that specified Due range
-
Genevieve P. Employee AdminJust to jump in here - you've got it! (In regards to when someone says that "No", a comment did not answer the question).
If the user who posts the question marksallof the comments below as "Rejected" or not answering the question, this question will appear in theUnansweredfeed, along with new ones. This is so that we can see what posts still require an accepted answer.
@MaricarmenThere are no negative actions taken for the people who posted the rejected answer! It's all good. That said, I've updated the comments back to being neutral.
:)
Cheers!
Genevieve
-
Paul Newcome ✭✭✭✭✭✭
@Genevieve P.Thanks for the info!
@MaricarmenThat's odd... I can't tell if your formula just got cut off by the screenshot/cell border, but make sure you are using exactly
=IF(COUNTIFS(CHILDREN(Due@row), @cell >= TODAY(-7)) > 0, 1)
If that is not the fix, let's start by removing the IF portion just to see exactly how many cells the formula is counting.
Also... Because the dates don't show the years, make sure you are using 2020 and not 2019. I know it seems silly, but sometimes it's the little things we don't think about.
-
Maricarmen Vargas ✭✭✭✭
@Paul Newcome, experimented a while and it seems that the parent is being checkmarked if any of the children Due dates are 7 days before Today or anytime after Today.
Example: Due 8 days before today or more
Due 7 days before today or anytime after today
Played around with the formula but couldn't get to the solution.
-
Paul Newcome ✭✭✭✭✭✭
Looking at it, that makes sense why it would do that. Give this a try...
=IF(COUNTIFS(CHILDREN(Due@row),AND(@cell >= TODAY(-7), @cell <= TODAY()) > 0, 1)
This should remove the dates that are past today.
-
Maricarmen Vargas ✭✭✭✭
@Paul NewcomeI think we are getting closer!!!
To count, edited it to this:
=COUNTIFS(CHILDREN(Due@row), AND(@cell <=TODAY(+7), @cell >= TODAY()))
Now I can just add a Checkmark to mark ifCountis more than 0.
However, still want to try too get the formula right without doing this workaround and learn from it.
The formula you shared with me is giving an error :(
=IF(COUNTIFS(CHILDREN(Due@row),AND(@cell >= TODAY(-7), @cell <= TODAY()) > 0, 1)
-
Paul Newcome ✭✭✭✭✭✭
@MaricarmenActually I think you may have it right. I was looking at dates in the PAST 7 days whereas you want NEXT 7 days. Your COUNTIFS is actually the correct one. That was my mistake.
Here...
=IF(COUNTIFS(CHILDREN(Due@row), AND(@cell <= TODAY(+7), @cell >= TODAY())) > 0, 1)
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+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-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+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-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
