Checkmark Parent if any Children Due Date is in the next 7 Days
Hello!
Stuck testing out formulas to add a Column [Children Due Soon] that checkmarks if a parent row [Task Name] has any children task is due [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

@Maricarmen Actually 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


Hi Paul, its a regular Date Select. I just changed the format of it :)

First time posting in the community, think I wasn't supposed to reject your comment @Paul Newcome .
Hope that didn't do anything negative!

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)

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.

@Paul Newcome Yes, 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 for help with Smartsheet, integrations, general workflow advice, or anything else.


@Paul Newcome You'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 for help with Smartsheet, integrations, general workflow advice, or anything else.

Not sure if I did something incorrectly, but the checkmark is still applied even when no children sub tasks have that specified Due range

Just 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 marks all of the comments below as "Rejected" or not answering the question, this question will appear in the Unanswered feed, along with new ones. This is so that we can see what posts still require an accepted answer.
@Maricarmen There 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

@Genevieve P. Thanks for the info!
@Maricarmen That'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.

@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.

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.

@Paul Newcome I 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 if Count is 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)

@Maricarmen Actually 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 the Formula Handbook template!