Scheduling issue with non-continuous time blocks

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
346
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

1656047995304.png
1656048652588.png
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top