Recognizing a gap in a schedule

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
314
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:
Excel Formula:
=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:
Excel Formula:
=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
1656281144769.png
1656281407241.png
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,176,670
Messages
5,904,378
Members
435,088
Latest member
Kingsof82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top