Scheduling issue with non-continuous time blocks

[gravanoc]

I am working with a workbook that assists physical trainers in scheduling time to meet clients. I'm trying to solve an issue where clients who are scheduled in non-consecutive time blocks (i.e. 8AM - 930AM then 1030AM - 12PM) appear to be scheduled for a continuous block (8AM - 12PM) on one of the tabs. I have taken a couple of screenshots to illustrate what is going on. On one tab the clients (or an activity) occupies the intersection of a time slot & a trainer. At the bottom of this matrix is the UNIQUE function, so each person/activity appears once below it. On the other tab in its Client column it pulls each of the unique clients/activities (up to 20 unique values), then in the Start Time column it checks the previous tab for a date match (using Start Date vs a group of columns in the previous tab that are on a certain date), and if there is a match then it performs a INDEX-MATCH to pull the earliest time for the client/activity. The Finish Time column formula is a little more involved, but essentially pulls the latest scheduled time for that client/activity from the previous tab. Lost in this are any time slots unoccupied by the client/activity, so they appear to be a continuous block of time.

I'm not really sure how to approach this, but one thought was that a formula that could detect gaps could be useful in helping to divide the problem into something easier to solve. If any additional info is required, please let me know.

1st Tab 2nd tab

 

[gravanoc]

I am working with a workbook that assists physical trainers in scheduling time to meet clients. I'm trying to solve an issue where clients who are scheduled in non-consecutive time blocks (i.e. 8AM - 930AM then 1030AM - 12PM) appear to be scheduled for a continuous block (8AM - 12PM) on one of the tabs. I have taken a couple of screenshots to illustrate what is going on. On one tab the clients (or an activity) occupies the intersection of a time slot & a trainer. At the bottom of this matrix is the UNIQUE function, so each person/activity appears once below it. On the other tab in its Client column it pulls each of the unique clients/activities (up to 20 unique values), then in the Start Time column it checks the previous tab for a date match (using Start Date vs a group of columns in the previous tab that are on a certain date), and if there is a match then it performs a INDEX-MATCH to pull the earliest time for the client/activity. The Finish Time column formula is a little more involved, but essentially pulls the latest scheduled time for that client/activity from the previous tab. Lost in this are any time slots unoccupied by the client/activity, so they appear to be a continuous block of time.

I'm not really sure how to approach this, but one thought was that a formula that could detect gaps could be useful in helping to divide the problem into something easier to solve. If any additional info is required, please let me know.

1st Tab 2nd tab

View attachment 67809View attachment 67810

Bump