Show dates beyond 90 days

Paul_Aitch

We need to report on Jobs that has not been touched in the last 90 days.

When we start a new Job we copy 4 "template" rows which include dates in the future.

When I run my current report, it looks for dates on the parent row, but this row is showing the oldest date for the children.

If a job has not been touched for over 90 days, but then is worked on again within the last 90 days, my parent row still shows the oldest date so is not picked up by the report.



An added complication is, because the template children rows contain dates in the future I cannot search for the latest date



I want my report to show The parent row only 

based on the following criteria for the children rows:

No children have a date that is within the last 90 days

No children have a date that is in the future



Thank you in advance

 

Paul Newcome

You would want to create a "Helper" checkbox column.

 

In this column, you would write an IF/AND statement that includes all of your criteria.

 

Something along the lines of

 

=IF(AND(COUNT(CHILDREN(Task@row)) > 0

 

to determine whether or not it is a parent row.

 

=IF(AND(COUNT(CHILDREN(Task@row)) > 0, COUNTIFS(CHILDREN([Date Column]@row, @cell >= TODAY(-90)) = 0

 

establishes that no children are greater than 90 days ago (which also covers no dates in the future).

 

=IF(AND(COUNT(CHILDREN(Task@row)) > 0, COUNTIFS(CHILDREN([Date Column]@row, @cell >= TODAY(-90)) = 0), 1)

and we tell it to check the box if it meets this criteria.

.

Let me know how it works for you.