I work at a hotel and we have scheduled crews staying with us. In an effort to better assign crew members to rooms with as little unoccupied time between stays AND also maintaining at least a given amount of time between stays for cleaning I've been trying to work out a rooming schedule.
I'll try to break it down:
in a tab titled 'days' is a table with all records of the dataset.
in the tab titled buffer there is one cell indicating a cutoff time in which a room is no longer cleanable on a given day, and below it, all known departure times in column A and the ideal end of cleaning times in column B.
then in the tab titled main, room numbers are populated down column A
Right of the rooms is where data for each day is populated. As you can see, 4 crew members departing after the cutoff time on 2-aug trigger a "-20" flag (this is internal jargon, but basically means the room is not usable until the next day.) Record numbers from the 'days' tab skip rows every time a -20 is present. This is a must.
Now for the part I'm stuck on...
I need to maintain the skipping rows where -20 is present but also now only assign records from the days tab if their arrival time is equal to or greater than the buffer time from the previous crew occupying the room. If it is not, the next room would be tested by a formula, basically distributing all expected crew on a given date to various rooms.
Looking forward to seeing what you geniuses put together! This forum has taught me much over the years, though I've only just registered in light of my new dilemma.
I'm sure more clarification will be needed and I'll be checking back constantly to do so!
Cheers,
Jordan
I'll try to break it down:
in a tab titled 'days' is a table with all records of the dataset.
| Book.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | arrdate | arrtime | depdate | deptime | record# | Column6 | Column7 | ||
| 2 | 01/08/22 | 7:28 | 03/08/22 | 16:25 | 10361 | 50FEXXHX | 16:25 | ||
| 3 | 01/08/22 | 21:10 | 02/08/22 | 22:00 | 9669 | 50TEDDHX | 22:00 | ||
| 4 | 01/08/22 | 21:10 | 02/08/22 | 22:00 | 9670 | 50TEDDHX | 22:00 | ||
| 5 | 01/08/22 | 21:31 | 02/08/22 | 22:05 | 9667 | 50TEDDHX | 22:05 | ||
| 6 | 01/08/22 | 21:31 | 02/08/22 | 22:05 | 9668 | 50TEDDHX | 22:05 | ||
| 7 | 02/08/22 | 6:10 | 04/08/22 | 5:41 | 13698 | 50MEXXHX | 05:41 | ||
| 8 | 02/08/22 | 6:10 | 04/08/22 | 5:41 | 13699 | 50MEXXHX | 05:41 | ||
| 9 | 02/08/22 | 7:28 | 04/08/22 | 16:25 | 10362 | 50FEXXHX | 16:25 | ||
| 10 | 02/08/22 | 7:28 | 04/08/22 | 16:25 | 10363 | 50FEXXHX | 16:25 | ||
| 11 | 02/08/22 | 11:39 | 03/08/22 | 5:35 | 9675 | 50TEDDHX | 05:35 | ||
| 12 | 02/08/22 | 11:39 | 03/08/22 | 5:35 | 9676 | 50TEDDHX | 05:35 | ||
| 13 | 02/08/22 | 12:45 | 03/08/22 | 7:15 | 9677 | 50TEDDHX | 07:15 | ||
| 14 | 02/08/22 | 12:45 | 03/08/22 | 7:15 | 9678 | 50TEDDHX | 07:15 | ||
days | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G2:G14 | G2 | =days!$D2 |
in the tab titled buffer there is one cell indicating a cutoff time in which a room is no longer cleanable on a given day, and below it, all known departure times in column A and the ideal end of cleaning times in column B.
| Book.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 4 | max | ||||
| 5 | 20:50 | ||||
| 6 | |||||
| 7 | deps | window | |||
| 8 | 16:25 | 17:55 | |||
| 9 | 22:00 | 23:30 | |||
| 10 | 22:05 | 23:35 | |||
| 11 | 05:41 | 07:11 | |||
| 12 | 05:35 | 07:05 | |||
| 13 | 07:15 | 08:45 | |||
| 14 | 11:05 | 12:35 | |||
| 15 | 15:40 | 17:10 | |||
| 16 | 12:55 | 14:25 | |||
| 17 | 15:05 | 16:35 | |||
| 18 | 12:35 | 14:05 | |||
| 19 | 21:35 | 23:05 | |||
| 20 | 13:55 | 15:25 | |||
| 21 | 21:40 | 23:10 | |||
| 22 | 20:20 | 21:50 | |||
| 23 | 11:00 | 12:30 | |||
| 24 | 07:25 | 08:55 | |||
| 25 | 06:45 | 08:15 | |||
| 26 | 05:05 | 06:35 | |||
| 27 | 17:40 | 19:10 | |||
| 28 | 00:00 | 01:30 | |||
buffer | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A8:A28 | A8 | =IFERROR(UNIQUE(days!$D$2:$D$1400),"") |
| B8:B28 | B8 | =$A8+1.5/24 |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
then in the tab titled main, room numbers are populated down column A
| Book.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | 30-Jul | |||||
| 2 | 701 | |||||
| 3 | 702 | |||||
| 4 | 716 | |||||
| 5 | 717 | |||||
| 6 | 718 | |||||
| 7 | 719 | |||||
| 8 | 720 | |||||
| 9 | 721 | |||||
| 10 | 801 | |||||
| 11 | 802 | |||||
| 12 | 816 | |||||
| 13 | 817 | |||||
main | ||||||
Right of the rooms is where data for each day is populated. As you can see, 4 crew members departing after the cutoff time on 2-aug trigger a "-20" flag (this is internal jargon, but basically means the room is not usable until the next day.) Record numbers from the 'days' tab skip rows every time a -20 is present. This is a must.
| Book.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| H | I | J | K | L | M | |||
| 1 | 01-Aug | 02-Aug | ||||||
| 2 | 10361 | 16:25 | 13698 | 05:41 | ||||
| 3 | 9669 | 22:00 | -20 | |||||
| 4 | 9670 | 22:00 | -20 | |||||
| 5 | 9667 | 22:05 | -20 | |||||
| 6 | 9668 | 22:05 | -20 | |||||
| 7 | 13699 | 05:41 | ||||||
| 8 | 10362 | 16:25 | ||||||
| 9 | 10363 | 16:25 | ||||||
| 10 | 9675 | 05:35 | ||||||
| 11 | 9676 | 05:35 | ||||||
| 12 | 9677 | 07:15 | ||||||
| 13 | 9678 | 07:15 | ||||||
main | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2:H13,K2:K13 | H2 | =IF(J2="-20","",IFERROR(INDEX(days!$A$2:$E$1400,SMALL(IF(ISNUMBER(MATCH(days!$A$2:$A$1400,H$1,0)),MATCH(ROW(days!$A$2:$A$1400),ROW(days!$A$2:$A$1400)),""),ROWS(H$1:H1)-COUNTIF(J$2:J2,"-20")),5),"")) |
| I2:I13,L2:L13 | I2 | =IFERROR(VLOOKUP(H2,days!$E$2:$G$1400,3,FALSE),"") |
| J2:J13,M2:M13 | J2 | =IFERROR(IF(VLOOKUP(E2,days!$E$2:$G$1400,3,FALSE)>buffer!$A$5,"-20",""),"") |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
Now for the part I'm stuck on...
I need to maintain the skipping rows where -20 is present but also now only assign records from the days tab if their arrival time is equal to or greater than the buffer time from the previous crew occupying the room. If it is not, the next room would be tested by a formula, basically distributing all expected crew on a given date to various rooms.
Looking forward to seeing what you geniuses put together! This forum has taught me much over the years, though I've only just registered in light of my new dilemma.
I'm sure more clarification will be needed and I'll be checking back constantly to do so!
Cheers,
Jordan
