+ Reply to Thread
Results 1 to 4 of 4

Rotating duty roster

  1. #1
    Registered User
    Join Date
    11-24-2011
    Location
    Chattanooga, TN
    MS-Off Ver
    Microsoft 365 MSO (Version 2303 Build 16.0.16227.20202) 64-bit
    Posts
    11

    Rotating duty roster

    Pulling hair out to figure out how to make this all come together:

    Variable number of employees depending on which office is using the sheet.
    One person scheduled for duty each day, Monday through Friday, except holidays.

    What I am trying to do, is list available people and Excel will simply assign a duty day, starting at the top and rotating through. When finished, it will start over.

    The exception would be if that person is on vacation or off.

    I have been playing with each day the sheet will look at the list of employees, compare to a list of people on vacation & people that have just served on duty and make a list of "available for duty". Then take the next on list.

    I think I am making a mountain out of a molehill.

    Can anyone suggest a better way?

    If wanted/needed, I can post my worksheet, but it really is a nonsensical mess right now. I am starting over.

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Rotating duty roster

    Perhaps you could use Solver?

    Solver is not installed by default in Excel 2003. To install it go to Tools-> Add-Ins and check box marked Solver.

    If not found select Browse -> Program Files -> Microsoft Office -> Office11 -> Library -> Solver -> Solver.xla Click on Solver.xla to install this add-in.

    To run Solver go to Tools -> Solver

    I've set up a small Solver model for staff planning that you could test.

    Alf
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-24-2011
    Location
    Chattanooga, TN
    MS-Off Ver
    Microsoft 365 MSO (Version 2303 Build 16.0.16227.20202) 64-bit
    Posts
    11

    Re: Rotating duty roster

    Thank you for the help. Unfortunately, no one in our office can be trusted to use "Add-Ins" and therefore we are restricted from adding "Solver".

  4. #4
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Rotating duty roster

    It should be possible. If I were doing it, I would have one tab labeled vacation, where the names would be grouped by department into three sections. Then, I would have the columns setup to be each workday of the year (in 2003, you should have just enough columns to do this). Put an X in each day an employee will be on vacation. In a separate spreadsheet, have a line for each department, showing the duty for that day.

    As for exact formula, we'll leave that until you've decided on a layout and put it on here as an example. There may be other better ideas from people who have done this before.
    Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.

    If you like a post, please rate it with the scales icon (top right).
    If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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