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

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

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

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    What column type is the [Due] column?

  • 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!

  • Paul NewcomePaul 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([email protected]), @cell >= TODAY(-7))


    Add in the IF:

    =IF(COUNTIFS(CHILDREN([email protected]), @cell >= TODAY(-7)) > 0, 1)

  • Paul NewcomePaul 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åAndrée Starå ✭✭✭✭✭

    @Paul Newcome Yes, you're correct. It only selects it as not being an accepted answer.

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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

  • Andrée Starå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.

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • edited 01/15/20

    @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


  • 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 NewcomePaul 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([email protected]), @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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Looking at it, that makes sense why it would do that. Give this a try...


    =IF(COUNTIFS(CHILDREN([email protected]), 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([email protected]), 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([email protected]), AND(@cell >= TODAY(-7), @cell <= TODAY()) > 0, 1)

  • @Paul Newcome thank you!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

Sign In or Register to comment.