Check for overlapping date ranges between sub tasks

Bas H
Bas H
edited 12/09/19 in Formulas and Functions


I am looking for a formula that can check for overlapping date ranges between of the sub tasks on a project. 

In the screenshot below the project has several sub tasks and I need a formula that will identify any overlap between the dates on the tasks.

I would like to prevent the use of references to cell numbers because I need to copy this formula across many lines. 

Would you have any suggestions on how to resolve this?

I have tried to use COLLECT and COUNTIF but it seems I cannot compare 2 ranges of child records. I can only compare 1 column to a specific cell 

Thank you very much !

Annotation 2019-08-21 165739.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So to be clear... You are basically trying to look at the start and finish dates for Task 1 and see if there is any overlap in the dates for the other tasks, then repeat this comparison for the remaining tasks?


    Are you trying to also compare Task 1 in Children Group A to dates in a separate group of Children rows, or are you only comparing within that same grouping of child rows?

  • Bas H
    Bas H
    edited 08/21/19

    Hi Paul, thanks for you reply and helping out with this. Your first statement is correct. 

    about the second statement:

    I am trying to compare the dates within the same grouping of children and not to other groups of children. 

    Kind regards


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Awesome! Ok. I remember helping someone set something similar up not too long ago. I will see if I can find my notes and get back to you.

  • Hello, I have the same question and condition as Bas, would like to know if we can calculate how many days are overlapping between Child task date range. And similarly, if that calculation can also help to count how many days are not overlapping from the child task date range as well. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!