Check dates against a range of dates for any overlap?

STEPHEN ALLEN

Hoping to get some guidance on formula to use.

Have a scheduled date range in which to get something done.  But need to see if there is any overlap of schedule dates against any blackout periods.

Sheet 1 has the task to be completed within a start date and end date window.

Sheet 2 has a list of blackout periods that work cannot occur.

So on Sheet 1, Rows Project 1&3 would have the "In Blackout Week" checked and the respective backout week period (from Sheet2) populated to sheet 1 "Blackout Start"  & "Blackout Over" columns.  (because there are dates in which the project dates overlap with a blackout period)

By way of example, Project 4 row, I manually clicked the "In Blackout Week" checked and I manually put in the respective backout week period that overlaps

Paul Newcome

Were you able to find a solution to this?

 

If not then I have a few ideas, but I would definitely need to do some testing to work out the details.

Lauren Mullane

I believe this comment may solve the problem.

https://community.smartsheet.com/discussion/40046/does-a-range-of-dates-fall-between-another-range-of-dates?

Paul Newcome

To check the box...

=IF(COUNTIFS({Other Sheet Blackout Start}, @cell <= [End Date]@row, {Other Sheet Blackout End}, @cell >= [Start Date]@row) > 0, 1)

Blackout Start for the overlap would be...

=INDEX(COLLECT({Other Sheet Blackout Start}, {Other Sheet Blackout Start}, @cell <= [End Date]@row, {Other Sheet Blackout End}, @cell >= [Start Date]@row))

Blackout end for the overlap...

=INDEX(COLLECT({Other Sheet Blackout End}, {Other Sheet Blackout Start}, @cell <= [End Date]@row, {Other Sheet Blackout End}, @cell >= [Start Date]@row))

Paul Newcome

@Lauren Mullane

The thread you linked to only solves for looking at one set of dates to overlap with. The checkbox portion of my above solution will solve for comparing to multiple date ranges.

Kate Folwell

Hey, @Paul Newcome-

I have a situation somewhat similar to this overlap example- but I can not quite capture the sum of overlapping time with a set of conditions. Perhaps you'd like to take another crack at a vexing overlapping time situation?

I am trying to capture the amount of time staff on one team takes to complete tasks (in any phase of work for any one deliverable in any one project) vs. another team. I am trying to capture how long various process steps/handoffs are taking in our overall process. I can collect the task time (using NETWORKDAYS formula in Day Count column below-not set up as a column formula in this example) on each row. I then use a helper columns to tell me if the task is being done by Team 1 or Team 2 (using cross sheet formulas- see description of helpers below).

I can collect the sum of work done by team 1 or team 2 in any phase/Deliverable (currently in summary fields).

My problem is that if any tasks overlap, I am double counting time, which turns my results into useless data. So I would like to check for any overlapping tasks done by the same team in the same phase/deliverable and capture the days spent on the task without double-counting any overlaps.

To solve this, I have been thinking of various ways to create:

1) Overlap helper: helper column that flags for overlap when team/phase/deliverable is the same (Used Countif formula that is trying to be like yours:

=IF(AND([TEAM Work 1]@row = 1, COUNTIFS([Deliverable Phase]:[Deliverable Phase], [Deliverable Phase]@row, [Deliverable #]:[Deliverable #], [Deliverable #]@row, [End Date]:[End Date], @cell >= [Start Date]@row, [Start Date]:[Start Date], @cell <= [End Date]@row) > 0), 1, 0)- I took out the OR part of the statement (to include Team 2 trigger) because I was trying to simplify this to figure out why it is not working. Right now it is flagging everything that team 1 does, but is not correctly addressing overlap (checking off dates that do not overlap).

2) Collect the min/max of the combined dates for overlapped flagged tasks in the same phase/deliverable. Using this for min column: IF([Overlap helper]=1,MIN(COLLECT([Start Date]:[Start Date], [Deliverable Phase]:[Deliverable Phase], [Deliverable Phase]@row, [Deliverable #]:[Deliverable #], [Deliverable #]@row))) This does give me the min for everything with same phase and deliverable #, and that meet above conditions- but I as helper column is not working, it is not useful as yet.

3) Sum Deliverable/phase/team task time without the overlap time double-counting.

I have helper columns galore (see screen shot).

Helper columns:

Indent level: Use to collect only actual task time- so any rows with [Indent Level]>2

Deliverable Phase and Deliverable #: Use to make sure I am comparing team work in the same phase and deliverable#.

Team 1 work, Team 2 Work: Used to check cross-referenced sheet for lists of names. Checked if contact is on one team or the other.

overlpa tst 2.PNG

If you can think of a formulas that would work for this- or an entirely better way to crack this nut, I am all open to any solution.

Thanks for any help - from Paul or anyone else that has any ideas on this!

Kate

Paul Newcome

@Kate Folwell I want to make sure I understand correctly. Lets se tasks 2 and 3 from Phase 1.

Task 2 Start = 12 Oct

Task 2 End = 14 Oct

Task 3 Start = 13 Oct

Task 3 End = 15 October

Lets assume that team 1 worked on both tasks. You would want it to count as 3 days (12th - 15th) and not 4 days (12th - 14th plus 13th - 15th)?

Kate Folwell

Exactly. Thanks, @Paul Newcome .

Paul Newcome

This can get a little tricky. We may have to tweak this solution after you have had a chance to play with it a bit.

Insert a text/number helper column called "Parent Helper" and enter this formula:

=IF(COUNT(CHILDREN([Project Task Name]@row)) = 0, PARENT([Project Task Name]@row))

Next we will insert another column called "Team 1 Start" that has this in it...

=IF(COUNT(CHILDREN([Project Task Name]@row)) = 0, IF(COUNTIFS([Parent Helper]:[Parent Helper], @cell = [Parent Helper]@row, [Team 1 Work]:[Team 1 Work], @cell = 1, [Start Date]:[Start Date], @cell<= [End Date]@row, [End Date]:[End Date], @cell >= Start Date]@row) > 0, MIN(COLLECT([Start Date]:[Start Date], [Parent Helper]:[Parent Helper], @cell = [Parent Helper]@row, [Team 1 Work]:[Team 1 Work], @cell = 1, [Start Date]:[Start Date], @cell<= [End Date]@row, [End Date]:[End Date], @cell >= Start Date]@row))))

Now we need a column called "Team 1 End" with this:

=IF(COUNT(CHILDREN([Project Task Name]@row)) = 0, IF(COUNTIFS([Parent Helper]:[Parent Helper], @cell = [Parent Helper]@row, [Team 1 Work]:[Team 1 Work], @cell = 1, [Start Date]:[Start Date], @cell<= [End Date]@row, [End Date]:[End Date], @cell >= Start Date]@row) > 0, MAX(COLLECT([End Date]:[End Date], [Parent Helper]:[Parent Helper], @cell = [Parent Helper]@row, [Team 1 Work]:[Team 1 Work], @cell = 1, [Start Date]:[Start Date], @cell<= [End Date]@row, [End Date]:[End Date], @cell >= Start Date]@row))))

This is only one piece of the solution, but I want to make sure this is working before we move on to the next piece. The result should output the earliest start date and the latest end date wherever there is overlap in dates for Team 1. This should be on each of the rows that contain overlapping dates.

Next steps will be to account for rows that have no overlap and then we will move on to only account for a single entry where there is overlap. From there we calculate the duration on each row which will be used to sum the totals. Once we have this all working, we can duplicate the solution (with a few minor tweaks) to do the same thing for Team 2.

Kate Folwell

@Paul Newcome Fie on me for not checking back sooner. In the last few says I created a solution that adds 40 (not kidding) column helpers that only covered conflicts with the row above (which only covers 95% of all plan conflicts within each subcategory).

I was trying to accommodate a comparison for every start/end date within the parent group, but could not come up with a "check the children of the parent for another column" to catch the row 1 end date >=Row 2 start date and other scenarios. It looks like your formula above is doing this to some extent.

I created the column formulas as you describe above. I attached the results in the real sheet here.

I think it is working to the extent you were expecting thus far.

Kate Folwell

SS Overlap.PNG

Sorry- attachment here @Paul Newcome

Ida Skou

Hi,

@Paul Newcome, can you possibly help me out with this as well. I can't get my formular to work.

I have to date columns: a start date column and an end date column, and I simply need to flag any rows, that have overlapping dates within the sheet.

I have tried these formulars, but they do not work (probably for obvious reasons I do not see)

=IF(AND(Startdate@row <= Enddate:Enddate; Enddate@row >= Startdate:Startdate; 1))

=IF(AND(Startdate@row <= Enddate:Enddate; Enddate@row >= Startdate:Startdate); 1)

Hope you can help me out,

Thank you.

Best, Ida

Paul Newcome

@Ida Skou You would need something more like this:

=IF(COUNTIFS(Enddate:Enddate; @cell>= Startdate@row; Startdate:Startdate; @cell<= Enddate@row)> 1; 1)

Ida Skou

@Paul Newcome Wow, that worked. Thank you so much. :)

hockenberry2d

@Paul Newcome

I have a similar situation here that I am trying to sort out.

I have a resources helper checkbox column setup with this formula...

=IF(COUNTIFS(Resources:Resources, HAS(@cell, "Gray Van"), [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1)

I am trying to make it check the box if Gray Van is one of the selected options in the Resources column AND the dates overlap another row that also has Gray Van selected. The picture below it has 2 rows checked but as you can see Gray Van is not selected in those rows.

Thanks in advance for any help you can provide!