Checkmark Parent if any Children Due Date is in the next 7 Days

Maricarmen Vargas

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!

Paul Newcome

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

Paul Newcome

What column type is the [Due] column?

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 Newcome Yes, you're correct. It only selects it as not being an accepted answer.

Paul Newcome

@Andrée Starå Thanks for the info. Good to know. 👍️

Andrée Starå

@Paul Newcome You're welcome!

I've talked with the community team about updating the wording because it can feel a little negative.

Maricarmen Vargas

@Paul Newcome

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.

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

Paul Newcome

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

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 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)

Paul Newcome

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