Is there a formula to have a cell checked when the due date is within 30 days of today?

Leann Gibson

I am trying to create a report that shows two items 1) Parent rows, 2) tasks under those that are due in 30 days from today. I can get the report to give me the tasks due in 30 days, but I am not getting the parents since the due dates are more than 30 days.

I was thinking I can create a helper column that automatically is checked when the due date is in 30 days and unchecked when not due within 30 days.

David Joyeuse

Hi @Leann Gibson

If you want to have the parent row displayed in the report, @Sean Morgan won't be enough I believe. It's perfectly fine for children rows, but Parents will remain out of it if there due date is in more than 30 days.

In the parent row you're gonna need to have this formula:

=IF(COUNTIF(CHILDREN(),1)>0,1,0)

This will check the box if any of the children is checked.

Now, if you want to automate the formula to be efficient on both praent and children rows, Use an helper column to determine the rank of the row:

Rank Column: =COUNT(ANCESTORS())+1

Then in your checkbox column:

=IF(Rank@row=1, IF(COUNTIF(CHILDREN(),1)>0,1,0),IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "Delayed"), [End Date]@row <= TODAY(30)), 1, 0))

Then you can only display rows where the box is checked, it will display items (tasks & parent rows) where due date is in the next 30 days.

Hope it helped!

Leann Gibson

Adding to this question. I want a Check Box column to check IF the End Date (title of column) is within 30 days of TODAY and the STATUS is In Progress, Not Started, or Delayed. My formulas are not working.

Sean Morgan

Hello @Leann Gibson ,

I was able to craft a formula that should fit this scenario. The formula I used was: =IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "Delayed"), [End Date]@row < TODAY(-30)), 1, 0)

Here is a Screenshot of this working with your chosen statutes, as well as a few different to demo the behaviour of the formula:

Let me know if you have any questions!

Regards

Sean

David Joyeuse

Hi @Leann Gibson

If you want to have the parent row displayed in the report, @Sean Morgan won't be enough I believe. It's perfectly fine for children rows, but Parents will remain out of it if there due date is in more than 30 days.

In the parent row you're gonna need to have this formula:

=IF(COUNTIF(CHILDREN(),1)>0,1,0)

This will check the box if any of the children is checked.

Now, if you want to automate the formula to be efficient on both praent and children rows, Use an helper column to determine the rank of the row:

Rank Column: =COUNT(ANCESTORS())+1

Then in your checkbox column:

=IF(Rank@row=1, IF(COUNTIF(CHILDREN(),1)>0,1,0),IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "Delayed"), [End Date]@row <= TODAY(30)), 1, 0))

Then you can only display rows where the box is checked, it will display items (tasks & parent rows) where due date is in the next 30 days.

Hope it helped!