Check for overlapping date ranges between sub tasks

Hi,

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

Find more posts tagged with

Formulas

Project Management

Comments

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

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

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.

SSP

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?