Task overlap

[webdevptg]

In a table I have some fields, Date, Start Time and Duration.
When adding a new task, I need to know if it will not overlap with existing ones. Below I send an image with 3 possible scenarios:

• In the first scenario, return 1 and that's correct, because the 4-hour task starting at 9:00 would overlap with the 11:00 that already exists on that date.
• In the second scenario it returns 1 but it is incorrect, because the new task starting at 9:00 am with a duration of 2 hours does not overlap with that of 11:00 am on the same date.
• In the third scenario it returns 0 and is incorrect, because the session starting at 12:00 will overlap with those at 11:00 am and has 2 hours

Help Please...

 

[GlennUK]

All the dates on the left are the same .... so, erm, what the hell is going on?????

 

[webdevptg]

Ops ... wrong date format. send the correct sheet

Link with editable sheet: Workbook1.xlsx

 

[GlennUK]

Overlap formula:

=SUMPRODUCT(--((G2+H2)<($A$2:$A$12+$B$2:$B$12+$C$2:$C$12/24))*--((G2+H2+I2/24)>($A$2:$A$12+$B$2:$B$12)))

 

[webdevptg]

Overlap formula:

=SUMPRODUCT(--((G2+H2)<($A$2:$A$12+$B$2:$B$12+$C$2:$C$12/24))*--((G2+H2+I2/24)>($A$2:$A$12+$B$2:$B$12)))

Works fine!

=SUMPRODUCT(--((E2+F2)<($A$2:$A$12+$B$2:$B$12+$C$2:$C$12/24))*--((E2+F2+G2/24)>($A$2:$A$12+$B$2:$B$12)))

 

[GlennUK]

Great. Thanks for feedback