+ Reply to Thread
Results 1 to 3 of 3

Shift scheduling by Seniority

  1. #1
    Registered User
    Join Date
    01-29-2022
    Location
    Chelsea, Michigan
    MS-Off Ver
    office 10
    Posts
    2

    Shift scheduling by Seniority

    Hi everyone,
    I'm new to the message board.
    I'm attempting to build a spreadsheet that schedules 3 shifts by seniority utilizing Vba.
    My Department has 147 employees, and every quarter we give them the opportunity to change their shift preference.
    The process is as follows. Each employee is given a 1st & 2nd choice of shifts. all shifts are manned in accordance by seniority.
    If a seniority conflict arises, such as multiple employees having the same seniority date.
    The solution is that those employees with the same dates be assigned seniority by alphabetical order (last name).
    As of right now we are doing this manually. Which as you can imagine, is very time consuming, and is prone to "human error".
    While I have done some fairly straight forward Vba programming, this one is causing me some issues.
    Any help, or guidance would be greatly appreciated.

    Thanks
    Tim.
    Attached Files Attached Files
    Last edited by rbrow250; 01-30-2022 at 02:18 AM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: Shift scheduling by Seniority

    I'm not sure what would be the good algoritm for that case to be utilized in VBA. But may be one could treat the task as a kind of optimization. And if optimization then excel is equipped with Solver tool.

    So please see the attached file.
    I pre-sorted the data to better see the results and easily setup starting assignments. The sort is by first choice of shift and seniority.

    Of course you gave too large numbers as required drivers, so I limited them to fit 25 drivers available.

    Then I preassigned the required numbers of all shifts starting top down all 1st, then from the row 16 with 4 2nd, then with 3rd shift and returned to blank rows and filled with remaining 2nd and 3rd shift

    and to run the solver there have to be a measure of the quality of fit. I used (you can modify this) that fitting first choice has the weight of 0.0001,second choice is 1 , the not-selected choice is 1000. and to include the seniority level i multiplied this value by the length of employment in days (so: today - start date).

    Then added all these mesured (discrepancies is probably not the best word, because they are never exactly 0 while we have some perfect fit cases, but they have to be non 0 to allow to promote staff with longer record of work - they will have larger TODAY()-D2 values so the solver will prefer to multiply this value by 0.0001 not by 1 )

    The sum of discrepancies is the value we want to minimize (the goal for the solver)

    The other constraints are pretty simple - shift being integer between 1 and 3. total drivers assigned to given shift = total needed drivers on this shift and solving method evolutionary.

    If you see the large number (larger than some 20 000 or so) in discreptancy column after a solver run, then it means that driver is assigned shift other than her/his first and second choice. You may try to run solver again or look at the data by yourself and may be you could spot a good substitution. It was not needed for 25 drivers (variables) but I expect that for 100+ it may be needed.

    See the attached file.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    01-29-2022
    Location
    Chelsea, Michigan
    MS-Off Ver
    office 10
    Posts
    2

    Re: Shift scheduling by Seniority

    Thank you very much for the direction. I've never used Solver before (never even heard of in until now). I was trying to use if, then statements, conditional formatting, and VBa.
    While I has some success it was extremely clunky, and not very used friendly. What you showed me is much more in line with what I had in mind. I'm currently exploring further into Solver for this, and some other spreadsheet issues my department has been having.
    Once again thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Seniority spreadsheet
    By pont463 in forum Excel General
    Replies: 6
    Last Post: 08-03-2019, 12:05 AM
  2. Rotating Seniority List
    By SapperT in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-30-2015, 10:09 AM
  3. 24X7x365 Employee Shift Scheduling
    By lbatto in forum Excel General
    Replies: 1
    Last Post: 05-04-2013, 10:38 PM
  4. Shift Scheduling
    By sean_weijie@hotmail. in forum Excel General
    Replies: 2
    Last Post: 03-10-2013, 11:21 AM
  5. Scheduling: Calendar style scheduling based on scheduling list
    By denkatprieto in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-19-2012, 11:10 AM
  6. Duplicates by seniority
    By Shadmani in forum Excel General
    Replies: 3
    Last Post: 04-09-2011, 12:13 PM
  7. Scheduling Employees: Linking Shift Times
    By zarlengp in forum Excel General
    Replies: 0
    Last Post: 09-22-2010, 02:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1