Overlapping Time (Schedules)

Your data is inconsistent. For example, on row 17 you have a start time of 9pm and a finish time of 3:30pm, on the same day! Should this be 3:30am on the next day?

Also, all your durations are shown as 2 hrs 30 mins.

Pete

Amazing, didn't expect a response that fast!

Hi, Glenn,

Sorry, schedule C should be tagged as X as well given that it overlaps with schedule B on a 1 hour duration. Although it's okay for schedule C to partially overlap with schedule D given that there's a 1 hour gap. Ex. 1-2, 2-3, 3-4 and 4-5 are perfect schedules.

Probably due to my poor explanation. Yes, I'm referring to individual lines. In a nut shell, Let's say these are training schedules of different individuals and I only have 1 trainer to cover everything hence I don't want overlapping schedules. The training duration is 2 hours and 30 mins per session.

Thanks in advance!

Originally Posted by Pete_UK

Your data is inconsistent. For example, on row 17 you have a start time of 9pm and a finish time of 3:30pm, on the same day! Should this be 3:30am on the next day?

Also, all your durations are shown as 2 hrs 30 mins.

Pete

Sorry Pete, I updated the file with the correct one.

I think it would help you if you extracted unique ranges, otherwise you are going to have a lot of "Y"s in your overlap column just because you have duplicated ranges for each attendee. To do this, you could have this formula in F2:

=IF(COUNTIFS(B$2:B2,B2,C$2:C2,C2,D$2:D2,D2)=1,MAX(F$1:F1)+1,"-")

Copy this down to the bottom of your data, and you will get a series of unique sequential numbers for each unique range encountered (plus a hyphen where the range is duplicated). Copy the headings from B1:D1 into H1:J1, then you can put this formula in H2:

=IF(ROWS($1:1)>MAX($F:$F),"",INDEX(B:B,MATCH(ROWS($1:1),$F:$F,0)))

which can be copied into I2:J2. Use the Format Painter icon to copy the format from B2:D2 into those cells (to give you date and time formats), and then copy these 3 formulae down until you start to get blanks. You can see by jumping down to the bottom of the data that the highest number in column F is 38, so you only need to copy these latest formulae down to a few more rows than this (e.g. to row 45) to ensure that you extract all the records. You will now have a list of the unique date and time ranges, and you might want a count of the number of people booked onto each course. Put the heading Count in cell K1, and this formula in K2:

=IF(H2="","",COUNTIFS(B:B,H2,C:C,I2,D:D,J2))

and copy this down.

This is the formula that I'm playing about with at the moment in L2 to check for overlaps:

=IF((COUNTIFS(H:H,H2,I:I,"<"&I2,J:J,">"&I2)+COUNTIFS(H:H,H2,I:I,"<"&J2,J:J,">"&I2))>1,"X","y")

but it is not quite right and I think that is because your time ranges butt right up to one another so I may need a few more <= or >=.

I'll have a coffee and give it a bit more thought.

Hope this helps.

Pete

Following on from before, put this formula in L2:

=IF(H2="","",IF((COUNTIF(H:H,H2)-COUNTIFS(H:H,H2,J:J,"<="&I2)-COUNTIFS(H:H,H2,I:I,">="&J2))=1,"X","y"))

then copy down.

I think that you should also put this formula in E2:

=D2-C2

and copy down, and examine the values. some show a duration of 2 hours instead of 2:30, and that is messing up some of the calculations for the overlap in column L.

Hope this helps.

Pete