Flagging a Date and Time Overlap Across Multiple Days

trevormain

Hi all,

I need some help with a formula which I believe I almost crack but I don't know what I'm still missing here, I want to flag overlapping tasks across multiple days and times, as you can see below, I have different columns: Start date, end date, start time, end time. Start and finish are just switching the time columns into numerical values. I need this formula to look any tasks that overlap based on date and time across days, (you can see the overlap with the gantt chart as well) using that "OK" column, currently my formula is like the one below, sometimes it works, but sometimes it doesn't flag the tasks even if they overlap, hopefully someone can help! thanks! much appreciated.

=IF(COUNTIFS([End Date]:[End Date], >=[Start Date]@row, [Start Date]:[Start Date], <=[End Date]@row, Finish:Finish, @cell >= Start@row, Start:Start, @cell <= Finish@row) > 1, 1)

unnamed.png

-T

James Keuning

First, I recommend you make one field that contains your date and time in one value as yyyymmddhhmm, which will appear as:

So now you are just dealing with Start and End. If this does not work, I am misunderstanding the problem.

From there, have you written out each test that you need run against each record? As I think through this problem, this is where I am getting hung up, and if you can write it out, I think we can develop a formula:

Obviously: Start is greater than Start AND End is less than End (S>S)+(E<E)

And I think something like, Start is greater than Start AND Start is less then END (S>S)+(S<E)

And I think End is less than End AND End is greater than Start. (E<E)+(E>S)

ARE THERE ANY OTHER TESTS THAT WILL IDENTIFY AN OVERLAP?

So let's say that covers it. So now we need to test each record against every other record. Create three fields, named with those bolded codes and use these formulas:

(S>S)+(E<E)=COUNTIFS(Start:Start, >Start@row, End:End, <End@row)

(S>S)+(S<E)=COUNTIFS(Start:Start, >Start@row, Start:Start, <End@row)

(E<E)+(E>S)=COUNTIFS(End:End, <End@row, End:End, >Start@row)

So now you will get some counts that you want to confirm, so let's built in some quality control. Create three more fields which will return the Row IDs of the records that are overlapping:

Join (S>S)+(E<E)=JOIN(COLLECT([Row ID]:[Row ID], Start:Start, >Start@row, End:End, <End@row), ",")

Join (S>S)+(S<E)=JOIN(COLLECT([Row ID]:[Row ID], Start:Start, >Start@row, Start:Start, <End@row), ",")

Join (E<E)+(E>S)=JOIN(COLLECT([Row ID]:[Row ID], End:End, <End@row, End:End, >Start@row), ",")

EDIT TO ADD: the quality control fields show you the Row ID of the row that meets the criteria.

What I have not done is collect all of the joined collects and deduplicate them and count unique values, in other words, for the first record, you have two conflicts, so you want to collect the 2,2,3,2 and pull out the unique values (a 2 and a 3) and count them.

I have not fully tested this. If you implement it and run some tests that show some failures, we can mitigate them.

James Keuning

I found another condition to test: (S<S)+(E>E)=COUNTIFS(Start:Start, <Start@row, End:End, >End@row)

Which leads to: (S<S)+(E>S)=COUNTIFS(Start:Start, <Start@row, End:End, >Start@row)

Join (S<S)+(E>E)=JOIN(COLLECT([Row ID]:[Row ID], Start:Start, <Start@row, End:End, >End@row), ",")

Join (S<S)+(E>S)=JOIN(COLLECT([Row ID]:[Row ID], Start:Start, <Start@row, End:End, >Start@row), ",")

What these additional conditions do is make it so that the test in Row 2 will identify Row 1, because in the example above, if Row 1 is showing overlap with Row 2, Row 2 should also show the overlap with Row 1. I am sure there is some description of this in formal logic vernacular but I do not know what it is.