Staff Scheduler

G

Gwill1983

Board Regular
Joined
Oct 24, 2018
Messages
124
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I believe what I am trying to achieve is impossible, but wanted to check with some experts before abandoning everything.

I am trying to create a staff scheduler which allocates staff to a role based on a 'hierarchy' of roles they are trained in taking staff requirement per department and leave into account.

So..... I have a table containing the staff hierarchy as below:

RankStaff 1Staff 2Staff 3Staff 4Staff 5Staff 6Staff 7Staff 8Staff 9Staff 10Staff 11
1​
SalesSalesPurchasingHRAdminSalesAdminAdminSalesHRHR
2​
PurchasingHRAdminSalesHRAdminSales
3​
AdminPurchasingHRSalesHRAdmin
4​
HR

This shows the staff member along the top and the list (in order) of the preffered role that they fulfill on a daily basis.

On a seperate tab, I have a holiday table showing each staff members booked holiday as below:

Employee Name01 Aug (Mon)02 Aug (Tue)03 Aug (Wed)04 Aug (Thu)05 Aug (Fri)06 Aug (Sat)07 Aug (Sun)08 Aug (Mon)09 Aug (Tue)10 Aug (Wed)11 Aug (Thu)12 Aug (Fri)13 Aug (Sat)14 Aug (Sun)15 Aug (Mon)16 Aug (Tue)17 Aug (Wed)18 Aug (Thu)19 Aug (Fri)20 Aug (Sat)21 Aug (Sun)22 Aug (Mon)23 Aug (Tue)24 Aug (Wed)25 Aug (Thu)26 Aug (Fri)27 Aug (Sat)28 Aug (Sun)29 Aug (Mon)30 Aug (Tue)31 Aug (Wed)
Staff 1WeekendWeekendWeekendWeekendWeekendWeekendWeekendWeekendStatutory Holidays - Summer Bank Holiday 2022
Staff 2WeekendWeekendWeekendWeekendWeekendWeekendWeekendWeekendWeekendWeekendWeekendWeekendStatutory Holidays - Summer Bank Holiday 2022
Staff 3Annual LeaveWeekendWeekendWeekendWeekendWeekendWeekendAnnual LeaveAnnual LeaveAnnual LeaveWeekendWeekendStatutory Holidays - Summer Bank Holiday 2022Annual LeaveAnnual Leave
Staff 4Annual Leave (Part Time) ( 06:30 Hours)Annual Leave (Part Time) ( 06:30 Hours)Annual Leave (Part Time) ( 06:30 Hours)Annual Leave (Part Time) ( 06:30 Hours)Annual Leave (Part Time) ( 05:30 Hours)WeekendWeekendWeekendWeekendWeekendWeekendAnnual Leave (Part Time) ( 07:00 Hours)WeekendWeekendStatutory Holidays - Summer Bank Holiday 2022
Staff 5WeekendWeekendWeekendWeekendAnnual LeaveAnnual LeaveAnnual LeaveWeekendWeekendWeekendWeekendStatutory Holidays - Summer Bank Holiday 2022
Staff 6WeekendWeekendWeekendWeekendWeekendWeekendWeekendWeekendStatutory Holidays - Summer Bank Holiday 2022
Staff 7WeekendWeekendWeekendWeekendWeekendWeekendWeekendWeekendStatutory Holidays - Summer Bank Holiday 2022
Staff 8WeekendWeekendWeekendWeekendWeekendWeekendWeekendWeekend
Staff 9WeekendWeekendAnnual LeaveAnnual LeaveWeekendWeekendAnnual LeaveAnnual LeaveAnnual LeaveAnnual LeaveAnnual LeaveWeekendWeekendAnnual LeaveWeekendWeekendStatutory Holidays - Summer Bank Holiday 2022
Staff 10WeekendWeekendWeekendWeekendWeekendWeekendWeekendWeekendStatutory Holidays - Summer Bank Holiday 2022
Staff 11WeekendWeekendWeekendWeekendWeekendWeekendAnnual Leave (Second Half)WeekendWeekendStatutory Holidays - Summer Bank Holiday 2022



What I aiming to do (and struggling) is have a master sheet which pulls the information on staff availability per day and then allocates their work role for that day based on the hierarchy chart.

I am struggling and about to throw in the towel as it appears to be a dead end.

Does anybody have any suggestions how to achieve this look up?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Any chance you could send in a workbook that has made up names and numbers? Mainly, to get a better understanding to see if I can help.
 
Upvote 0
Is there a set limit amount of sales/admin/hr/purchasing that you want? And, how do you choose who is next? Is it based on staffing order as well?
 
Upvote 0
ZMyrrh said:
Is there a set limit amount of sales/admin/hr/purchasing that you want? And, how do you choose who is next? Is it based on staffing order as well?
Click to expand...
Thanks for the reply. How do I send in a workbook for you to look at? More than happy to do so, just not sure how!

I would want to set a staffing number requirement per day for each department and the sheet then allocate the staff based on the rank of preferences. For instance. If the Sales dept needed 2 staff members then it would take 2 with 'Sales' in rank 1. If one of these people were unavailable then it would take somebody with sales in rank 2
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Scheduling running staff hours
Replies
1
Views
347
awoohaw
awoohaw
J
  • Solved
Formula Filled Dates
Replies
4
Views
479
Fluff
Fluff
S
  • Question Question
Calculate Turn Around Time 7 days working excluding Holiday and in Working Hours
Replies
3
Views
776
surevyas1984
S
D
  • Solved
IF, IF(And(countif)) formula not working correctly
Replies
5
Views
481
SanjayGMusafir
SanjayGMusafir
S
  • Solved
Calculate Revert Time
Replies
6
Views
668
felixstraube
F

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

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