Building a rota - check time slot availability

L

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
246
Office Version
  1. 365
Platform
  1. Windows
Hi all,

This one seems like a biggy. I've got a few ideas of how to run parts of what I'm trying to do here, but before I get into that I'll show my before and after sheets.

Before:
Rota 3.xlsm
ABCDEFG
1EmployeeEmployee 1Employee 2Employee 3Employee 4Employee 5
2Blank Row
3Time09:00 - 17:0009:00 - 17:0009:00 - 17:0009:00 - 17:0009:00 - 17:00
409:0009:15AvailableAvailableUnavailableUnavailableAvailable
509:1509:30AvailableAvailableAvailableUnavailableAvailable
609:3009:45AvailableUnavailableAvailableUnavailableAvailable
709:4510:00AvailableUnavailableAvailableUnavailableAvailable
810:0010:15AvailableUnavailableAvailableUnavailableAvailable
910:1510:30AvailableUnavailableAvailableUnavailableAvailable
1010:3010:45AvailableUnavailableAvailableAvailableAvailable
1110:4511:00AvailableUnavailableAvailableAvailableAvailable
1211:0011:15UnavailableUnavailableAvailableAvailableAvailable
1311:1511:30UnavailableUnavailableAvailableAvailableAvailable
1411:3011:45AvailableUnavailableAvailableAvailableAvailable
1511:4512:00AvailableUnavailableAvailableAvailableAvailable
1612:0012:15AvailableUnavailableAvailableAvailableAvailable
1712:1512:30AvailableUnavailableAvailableAvailableAvailable
1812:3012:45AvailableUnavailableAvailableAvailableAvailable
1912:4513:00AvailableUnavailableAvailableAvailableAvailable
2013:0013:15AvailableUnavailableUnavailableAvailableAvailable
2113:1513:30AvailableUnavailableUnavailableAvailableUnavailable
2213:3013:45AvailableAvailableUnavailableAvailableUnavailable
2313:4514:00AvailableAvailableAvailableAvailableUnavailable
2414:0014:15UnavailableAvailableAvailableAvailableAvailable
2514:1514:30UnavailableAvailableUnavailableAvailableAvailable
2614:3014:45UnavailableAvailableUnavailableAvailableAvailable
2714:4515:00UnavailableAvailableUnavailableAvailableAvailable
2815:0015:15UnavailableAvailableAvailableAvailableAvailable
2915:1515:30UnavailableAvailableAvailableUnavailableAvailable
3015:3015:45UnavailableUnavailableUnavailableUnavailableAvailable
3115:4516:00UnavailableAvailableUnavailableAvailableUnavailable
3216:0016:15UnavailableAvailableUnavailableAvailableUnavailable
3316:1516:30UnavailableAvailableAvailableAvailableAvailable
3416:3016:45UnavailableAvailableAvailableAvailableAvailable
3516:4517:00UnavailableAvailableAvailableAvailableAvailable
Sheet1
Cell Formulas
RangeFormula
B4:B35B4=A4+TIME(0,15,0)
A5:A35A5=B4


After:
Rota 3.xlsm
ABCDEFGH
1EmployeeEmployee 1Employee 2Employee 3Employee 4Employee 5
2Blank Row
3Time09:00 - 17:0009:00 - 17:0009:00 - 17:0009:00 - 17:0009:00 - 17:00
409:0009:15Activity 1AvailableUnavailableUnavailableAvailable
509:1509:30Activity 1AvailableAvailableUnavailableAvailable
609:3009:45Activity 1UnavailableAvailableUnavailableAvailable
709:4510:00Activity 1UnavailableAvailableUnavailableAvailable
810:0010:15AvailableUnavailableAvailableUnavailableAvailable
910:1510:30AvailableUnavailableAvailableUnavailableAvailable
1010:3010:45AvailableUnavailableAvailableAvailableAvailable
1110:4511:00AvailableUnavailableAvailableAvailableAvailable
1211:0011:15UnavailableUnavailableAvailableAvailableAvailable
1311:1511:30UnavailableUnavailableAvailableAvailableAvailable
1411:3011:45AvailableUnavailableAvailableAvailableAvailable
1511:4512:00AvailableUnavailableAvailableAvailableAvailable
1612:0012:15AvailableUnavailableAvailableActivity 2Activity 2
1712:1512:30AvailableUnavailableAvailableActivity 2Activity 2
1812:3012:45AvailableUnavailableAvailableActivity 2Activity 2
1912:4513:00AvailableUnavailableAvailableActivity 2Activity 2
2013:0013:15AvailableUnavailableUnavailableAvailableAvailable
2113:1513:30AvailableUnavailableUnavailableAvailableUnavailable
2213:3013:45AvailableAvailableUnavailableAvailableUnavailable
2313:4514:00AvailableAvailableAvailableAvailableUnavailable
2414:0014:15UnavailableAvailableAvailableAvailableAvailable
2514:1514:30UnavailableAvailableUnavailableAvailableAvailable
2614:3014:45UnavailableAvailableUnavailableAvailableAvailable
2714:4515:00UnavailableAvailableUnavailableAvailableAvailable
2815:0015:15UnavailableAvailableAvailableAvailableAvailable
2915:1515:30UnavailableAvailableAvailableUnavailableAvailable
3015:3015:45UnavailableUnavailableUnavailableUnavailableAvailableActivity 3
3115:4516:00UnavailableAvailableUnavailableAvailableUnavailableActivity 3
3216:0016:15UnavailableAvailableUnavailableAvailableUnavailable
3316:1516:30UnavailableAvailableAvailableAvailableAvailable
3416:3016:45UnavailableAvailableAvailableAvailableAvailable
3516:4517:00UnavailableAvailableAvailableAvailableAvailable
Sheet2
Cell Formulas
RangeFormula
B4:B35B4=A4+TIME(0,15,0)
A5:A35A5=B4


Reference Data:
ActivityStart timeFinish timeStaff required
Activity 109:0010:001
Activity 212:0013:002
Activity 315:3016:001


So what I'm trying to do is get a script to take data from the activity table and place it on the rota where "available".

Requirements are that an activity cannot be split up - for example, on the table employees 1, 2 & 5 are "available" at 9, but only 1 & 5 have the full 4 15 minute slots (the one hour of activity 1) so only employees 1 & 5 can be given activity 1.

I would like the code to have some sort of randbetween function where any employee with the full number of slots available at the correct times for an activity is picked from at random, rather than it being given to simply the first employee available - so in the example Activity 1 could have been given to Employee 1 or 5, Activity 2 could have gone to any combination of 1,3,4 and 5 .

Any activity where no employee is available should be dumped on the right hand side (first column with no employee - so H in the example with activity 3).

I should also note that my actual sheet has more that 4 employees but varies (so xlRight), times on the actual sheet go from A4 to A148, and the activities table is just an example, the real one will also have a variable amount of activities that last for various amounts of time (but always in 15 minute intervals). The last column of this table denotes how many staff members are required for that given activity (never more than this number, but if not all can be met, dump the excess to the right as before) - very unlikely to go over 2 but some flexibility there wouldn't hurt.

I appreciate this is a big ask (or I think it is as I only know how to do individual bit of the requirements - not all together) so any help or input would be a big help here.

Thanks all!

All formatting (colours, fonts etc) are already handled.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You must log in or register to reply here.

Similar threads

N
  • Solved
Massive Data Analysis needing a helper column
Replies
9
Views
621
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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