+ Reply to Thread
Results 1 to 2 of 2

Creating Schedules with Excel and VBA

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2012
    Location
    Warsaw, Poland
    MS-Off Ver
    2007/2010
    Posts
    555

    Creating Schedules with Excel and VBA

    Hi all,

    Did anyone on this forum ever try to create a excel sheet which would allow to create sophisticated schedules for employees? By sophisticated i mean schedules with:
    - A requirement forecast (e.g. 6:30 - 2 Heads needed, 7:00 3 Heads needed ... 14:00 15 Heads needed etc.).
    - Multiple Shift lengths - 4,5,6,7,8,9 hrs length
    - Start times (e.g. a given shift can start only at 8:00, 9:00 or 10:00 while another one can start at 8:00, 8:30, 9:00, 9:30 etc.)
    - Hrs in a week - the sum of all shift lengths assigned by the vba code should equal a given number (e.g. 40).
    - Start time consistency - If a given shift starts at 8:00 on Monday, it can start only within +-1hr during the rest of the week.

    I imagined a combination of excel functions/formulas and some vba code to to the job. The biggest "concern" lies in the code that would assign the individual shifts - Lets say that the head(hours) requirement equals 100 working hours on a monday and has a normal distribution. Someone has created the shift rules etc. already and assigned them to "names". It turned out that there can be only 80 hours distributed on that monday (10x8hrs shifts to make it simple). The vba code would have to figure out that there are not sufficient hours to distribute to cover the requirements completely and would have to make the best of it anyway (e.g. not leaving the last 2 hours without any schdules - that would be an easy mistake if the code goes from the morning till evening while distributing shifts etc.).

    I'm sure that there are commercial applications that do such tasks. I'd like to try to create something on my own though. I'm not asking for anything specific - just some thoughts on the problem whether experienced people think that this is doable in excel - or if I shouldn't waste my time on such a task). Perhaps someone did indeed try to make something similar work? Or perhaps someone has worked with some algorythms that would make it easier to create the part that would generate the best possible schedule with the given constraints?

    Thanks for your time!
    Last edited by Bishonen; 06-05-2013 at 12:50 PM.
    If you think that my answer was helpful, please click on the "Add to this user's Reputation" button.

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Creating Schedules with Excel and VBA

    It's certainly a do-able and I would expect it to be predominantly VBA but that's just my personal bias/preference of approach. I did a slightly similar project a few years ago which required establishing the servicing schedules for 160 machines of various types which proved to be an invaluable planning aid.. It would take some doing but will pay dividends in the long run, if done well.
    Elegant Simplicity............. Not Always

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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