+ Reply to Thread
Results 1 to 1 of 1

Roster schedule planning

  1. #1
    Registered User
    Join Date
    07-18-2015
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    MS Office 2013
    Posts
    1

    Roster schedule planning

    Dear All,
    I want to make a roster to schedule. Its a schedule for four days on 20+ locations with three persons per shift and shifts of 2 hours. Please find attached spreadsheet. On the availability tab you will find the total availability per person per day. And on the other tabs you will find the days where these people should be scheduled in a team of 3 persons per shift of 2 hours and 1 shift a day per person.
    Is there a way or formula to create such a roster? Otherwise I will have to this manually.
    Thank you very much for your help.
    rch
    Attached Files Attached Files
    Last edited by rch.rjch; 07-18-2015 at 08:55 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,609

    Re: Roster schedule planning

    This is an interesting but not-so-easy task and everybody who tried to do it manually knows about it :-)

    Moreover, for the dummy data you supplied only Saturday can be properly scheduled. For instance in Wed you have just 363 persons available (at any time) while you need 486 (=162*3) people.

    As for data, I've two additional comments
    1) I removed from original file few cells with single space (looks like empty cell) and replaced all texts like half day etc.) with your format hh:mm-hh:mm
    2) Of course if somebody is avaliable 13:00-15:00 this person cannot be assigned to any shift 08:00-10:00, 10:00-12:00 etc.

    See the attached file (macro is not very quick, so observe status bar - the day macro is working on is displayed there).

    What I like about this macro is that it optimizes staff scheduling and starts assigning with least favorable hours, and staff with most restricted availability.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Best Regards,

    Kaper

+ 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. How do I create a rotating roster for a 10 week on 2 week off schedule
    By MacknMeggs78 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-02-2014, 09:18 PM
  2. Using excel to schedule roster
    By shairazi in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 11-24-2013, 11:53 AM
  3. Replies: 1
    Last Post: 09-29-2013, 04:33 AM
  4. Making a time planning schedule
    By ImNoTree in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-16-2013, 01:48 PM
  5. Replies: 8
    Last Post: 07-15-2013, 07:13 PM
  6. Help with setting up a shift schedule/roster!
    By target.aquired in forum Excel General
    Replies: 3
    Last Post: 08-07-2012, 05:29 AM
  7. [SOLVED] Create patient schedule based on master therapist schedule
    By PapaBear in forum Excel General
    Replies: 8
    Last Post: 10-12-2005, 12:05 AM

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