Dear team,
I am faced with a unique situation and would really seek help here.. I work with an organization that has about 5000 staff members, who are working out of 159 branches. Each branch has a different seating capacity - now some branches have traditionally had more employees than the seating capacity. The post lockdown norms enforce physical distancing - due to which we are allowed to have 50% staffing of the total seats in the branch.
The new norms have 3 shift codes - WFF (Work from Field), WFO (Work from office) and WFH (Work from home).. while there is no restriction to the number of people who can be on WFH / WFF, you can only have 50% of the available seat count allowed to WFO. Employees who are on WFH / WFF may have to come to the office anywhere between 1 to 5 days in a week.
Now to tackle this I have created 3 rows above the data detailing Date, Week No. and Day. The rule is that for each branch code there is a restriction on the number of seats available. The total number of employees on WFO cannot exceed the seating limit per day, and cannot exceed the number of days in office per week. (The image below is a overly simplified view of the process) .. WO stands for week off.
I tried to write a formula ignoring circular references - but could not get the randomization of the days in the week possible. attaching a copy of the dummy sheet I made here - any help will be deeply appreciated.
I am faced with a unique situation and would really seek help here.. I work with an organization that has about 5000 staff members, who are working out of 159 branches. Each branch has a different seating capacity - now some branches have traditionally had more employees than the seating capacity. The post lockdown norms enforce physical distancing - due to which we are allowed to have 50% staffing of the total seats in the branch.
The new norms have 3 shift codes - WFF (Work from Field), WFO (Work from office) and WFH (Work from home).. while there is no restriction to the number of people who can be on WFH / WFF, you can only have 50% of the available seat count allowed to WFO. Employees who are on WFH / WFF may have to come to the office anywhere between 1 to 5 days in a week.
Now to tackle this I have created 3 rows above the data detailing Date, Week No. and Day. The rule is that for each branch code there is a restriction on the number of seats available. The total number of employees on WFO cannot exceed the seating limit per day, and cannot exceed the number of days in office per week. (The image below is a overly simplified view of the process) .. WO stands for week off.
I tried to write a formula ignoring circular references - but could not get the randomization of the days in the week possible. attaching a copy of the dummy sheet I made here - any help will be deeply appreciated.
| Book4 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Day : | Mon | Tue | Wed | Thu | Fri | Sat | Sun | ||||||||
| 2 | Week No. | 25 | 25 | 25 | 25 | 25 | 25 | 25 | ||||||||
| 3 | Date : | 15-Jun | 16-Jun | 17-Jun | 18-Jun | 19-Jun | 20-Jun | 21-Jun | ||||||||
| 4 | Emp. Id | Employee Name | Branch Code | Seats Available | Employees rostered in the branch | Primary Role | Days in office | |||||||||
| 5 | 10000 | Emp 1 | AHM003 | 2 | 4 | WFH | 1 | WFH | WFH | WFO | WFH | WFH | WO | WO | ||
| 6 | 10001 | Emp 2 | AHM003 | 2 | 4 | WFH | 2 | WFO | WFH | WFH | WFH | WFO | WO | WO | ||
| 7 | 10002 | Emp 3 | AHM003 | 2 | 4 | WFF | 3 | WFF | WFO | WFO | WFO | WFH | WO | WO | ||
| 8 | 10003 | Emp 4 | AHM003 | 2 | 4 | WFH | 3 | WFO | WFO | WFH | WFO | WFH | WO | WO | ||
| 9 | 10004 | Emp 5 | AHM004 | 3 | 4 | WFH | 1 | |||||||||
| 10 | 10005 | Emp 6 | AHM004 | 3 | 4 | WFF | 3 | |||||||||
| 11 | 10006 | Emp 7 | AHM004 | 3 | 4 | WFH | 2 | |||||||||
| 12 | 10007 | Emp 8 | AHM004 | 3 | 4 | WFH | 2 | |||||||||
Sheet1 | ||||||||||||||||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| H5:N8 | Cell Value | ="WO" | text | NO |
| H5:N8 | Cell Value | ="WFF" | text | NO |
| H5:N8 | Cell Value | ="WFO" | text | NO |
| H5:N8 | Cell Value | ="WFH" | text | NO |
