Recognizing a gap in a schedule

[gravanoc]

Please see the screenshots below for reference. The first pic is from a tab named ScheduleOverview, the 2nd is from a tab named Import. Across vertically on ScheduleOverview are time slots in 15-min increments from 7A-7P where clients can be scheduled, while across the top horizontally are the names of 20 trainers, and there are 5 sections for each day M-Fri. If a person or activity is scheduled they will be filled in for each time slot in that period of time, i.e. if Dave is scheduled from 2p-3p he will occupy 4 time slots, 2p, 2:15p, 2:30p, 2:45p. Below the scheduling matrix in each trainer's column these people/activities are condensed by a formula to one instance, basically the same as the UNIQUE function, so using Dave as an example again he would show up once in the order he appeared in the column in.

On the Import tab the relevant columns are Start Date, End Date, Start Time, Finish Time & Client/Activity Name. Client/Activity Name, Start Date & End Date are not an issue; the problem is that Start & Finish Time have a formula that overlooks occurrences where a client or activity have a break, or gap in time blocks. Dave might be scheduled for 2p-3p, a 15 minute break, then from 3:15-4p, but his Start Time will show 2p and his Finish Time will be 4pm. I would like to fix this so that breaks are accounted for. Here is the formula used for Start Time:

=IFERROR(IF($D2=ScheduleOverview!$J$1,INDEX(ScheduleOverview!$I$3:$I$51,MATCH($H2,ScheduleOverview!J$3:J$51,0)),IF($D2=ScheduleOverview!$AE$1,INDEX(ScheduleOverview!$I$3:$I$51,MATCH($H2,ScheduleOverview!AE$3:AE$51,0)),IF($D2=ScheduleOverview!$AZ$1,INDEX(ScheduleOverview!$I$3:$I$51,MATCH($H2,ScheduleOverview!AZ$3:AZ$51,0)),IF($D2=ScheduleOverview!$BU$1,INDEX(ScheduleOverview!$I$3:$I$51,MATCH($H2,ScheduleOverview!BU$3:BU$51,0)),IF($D2=ScheduleOverview!$CP$1,INDEX(ScheduleOverview!$I$3:$I$51,MATCH($H2,ScheduleOverview!CP$3:CP$51,0)),""))))),"")

And for Finish Time:

=IFERROR(@IF($D2=ScheduleOverview!$J$1,INDEX(ScheduleOverview!$I$3:$I$51,SUMPRODUCT(MAX(ROW(ScheduleOverview!J$3:J$51)*($H2=ScheduleOverview!J$3:J$51))-1)),IF($D2=ScheduleOverview!$AE$1,INDEX(ScheduleOverview!$I$3:$I$51,SUMPRODUCT(MAX(ROW(ScheduleOverview!AE$3:AE$51)*($H2=ScheduleOverview!AE$3:AE$51))-1)),IF($D2=ScheduleOverview!$AZ$1,INDEX(ScheduleOverview!$I$3:$I$51,SUMPRODUCT(MAX(ROW(ScheduleOverview!AZ$3:AZ$51)*($H2=ScheduleOverview!AZ$3:AZ$51))-1)),IF($D2=ScheduleOverview!$BU$1,INDEX(ScheduleOverview!$I$3:$I$51,SUMPRODUCT(MAX(ROW(ScheduleOverview!BU$3:BU$51)*($H2=ScheduleOverview!BU$3:BU$51))-1)),IF($D2=ScheduleOverview!$CP$1,INDEX(ScheduleOverview!$I$3:$I$51,SUMPRODUCT(MAX(ROW(ScheduleOverview!CP$3:CP$51)*($H2=ScheduleOverview!CP$3:CP$51))-1)),"")))) ),"")

This is a X-Post also at: Reddit