Formula to check box if child rows include due date within the quarter

Melissa Guthrie CDA · 07/02/24

I am trying to create a helper column on my sheet that checks a box if the child rows have a due date within the quarter. Our quarters are based on the fiscal year July 1 to June 30. So far I have the Q1 helper column with this formula which is getting and UNPARSEABLE error =IF(CHILDREN([Due Date]:[Due Date]) MONTH([Due Date] >6,YEAR(Due Date]) +1),1,0).

Melissa Guthrie CDA · 07/12/24

I figured it out! I am posting here in case someone else needs help. This formula checks the parent column if the child has a due date that falls within the quarter. I have a report that pulls in tasks due within that quarter, but I wanted to bring in the parent row as well. By using this formula I can now filter on my quarter helper column to bring the parent in.

I did take Dan Palenchar's recommendation above and defined my quarters using the sheet summary feature. I then created a helper column for each quarter and used the following formulas.

Q1 - =IF(COUNT(CHILDREN(Goals@row)) > 0, IF(COUNTIFS(DESCENDANTS([Due Date]@row), >=[Q1 Start]#, DESCENDANTS([Due Date]@row), <[Q2 Start]#) > 0, 1, 0))

Q2 - =IF(COUNT(CHILDREN(Goals@row)) > 0, IF(COUNTIFS(DESCENDANTS([Due Date]@row), >=[Q2 Start]#, DESCENDANTS([Due Date]@row), <[Q3 Start]#) > 0, 1, 0))

Q3- =IF(COUNT(CHILDREN(Goals@row)) > 0, IF(COUNTIFS(DESCENDANTS([Due Date]@row), >=[Q3 Start]#, DESCENDANTS([Due Date]@row), <[Q4 Start]#) > 0, 1, 0))

Q4- =IF(COUNT(CHILDREN([Action ID]@row)) > 0, IF(COUNTIFS(DESCENDANTS([Completion Progress]@row), >=[Q4 Start]#, DESCENDANTS([Completion Progress]@row), <[Next Year]#) > 0, 1, 0))

If you struggle with formulas as I do here are some helpful tips:

When using the Children formula in the first part of the equation reference the column that has the parent/child relationship. You are essentially saying if this row has children continue with the subsequent formula string. (This sounds obvious but it took me a long time to figure it out).

The next IF statement asks it to search the date column of its descendants and count it if it exists within the date range (AKA between Q1 & Q2, etc).

The final piece asks it to check the box if true and leave it unchecked if not true.

Since I have multiple parent/children hierarchies within my sheet, and only wanted the direct parent, I added my formulas to the specific parent rows I wanted to bring in. However, you could make this a column formula if you want to bring in all the parents.

I hope this helps someone else!

SoS | Dan Palenchar · 07/03/24

Hello @Melissa Guthrie CDA,

How to check if a date falls in a quarter

I've solved a similar question to this in a Published Sheet you can access here or below (make sure to open the Sheet summary on the right hand side).

If you add these Sheet Summary Fields to your Sheet you can use this formula to check for a Date being in quarter 1. Replace Date with [Due Date] in your case.

=IF(AND(Date@row >= [Q1 Start]#, Date@row < [Q2 Start]#), 1)

https://app.smartsheet.com/b/publish?EQBCT=cbbaf5decbc84e1c9178487e8dc7f0a6

I have recorded a YouTube on this but we're currently editing it, you can check this report to see when it is live or subscribe to the School of Sheets Channel if you are interested in seeing it.

Formula best practices

I want to point a few errors in your formula to help you in the future.

First, you need to reference a range or an individual cell with a row number or @row. I've pasted your formula below and added # signs to show where this is missing.

=IF(CHILDREN([Due Date]:[Due Date]) MONTH([Due Date]# >6,YEAR(Due Date]#) +1),1,0).

Next, when using CHILDREN() in this fashion select the single parent cell whose children you wish to evaluate like:

=IF(CHILDREN([Due Date]@row)…

Then, when using MONTH(), the parentheses need to close around a date.

=MONTH([Due Date]@row)

However, to get the functionality you want I would structure the formula differently by

Hope this helps!

Melissa Guthrie CDA · 07/03/24

Thank you so much for your help! This formula partially got me there. Using the sheet summary to establish the quarters is very helpful. However, my main issue is that I want this formula to check the parent column if any of the children have a date in that quarter, not the children themselves. I will later be using this column to filter anything with a checkbox so I can pull the parent column into a quarterly report. Do you know how to tweak the formula to achieve this?

Melissa Guthrie CDA · 07/12/24