+ Reply to Thread
Results 1 to 7 of 7

Daily Schedule for 8 Employees

  1. 10-26-2017, 08:32 PM #1
    Rigged
    Rigged is offline
    Registered User
    Join Date
    09-17-2012
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    15

    Daily Schedule for 8 Employees

    Hey Folks!

    The basic premise is to have a daily schedule (one employee per day) with a total of 8 employees (8 person rotation). Here is the catch, an employee can't have a Friday, Saturday, or Sunday consecutively. So if an employee worked on a Friday they would have a Monday, Tuesday, Wednesday, or Thursday as their next coverage day.

    I can build this out manually but I always end up with coverage days that are very close and I know that there has got to be a way to evenly distribute the employees throughout the year.

    I've just about run out of ideas for this sheet/formula. I've tried a dynamic COUNTIFS, SUMIFS, FREQUENCY, and INDEX formulas and I just can't get any progress.

    Any help would be hugely appreciated.

    Thanks!
    Kyle
    Reply With QuoteRegister To Reply
  2. 10-26-2017, 08:59 PM #2
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Daily Schedule for 8 Employees

    It is not very clear for me. Is it each day there is only 1 employee works? So after working day, a pacticular person is off for next 7 days?
    Could you upload a sample file?
    Quang PT
    Reply With QuoteRegister To Reply
  3. 10-26-2017, 09:09 PM #3
    Rigged
    Rigged is offline
    Registered User
    Join Date
    09-17-2012
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Daily Schedule for 8 Employees

    Absolutely!

    So once an employee works for that single day they can be off for any number of days, but throughout the year every employee should have the same total days worked or close to it.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 10-26-2017, 10:18 PM #4
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Daily Schedule for 8 Employees

    Hi all- I re-mapped the weeks to meet the sequencing requirement, then filled in employees on the week/weekday grid.
    The following are named ranges used in various formulae: pattern, rotation, weekdays, and Employees
    Here's the formula for week re-map (yellow) in B2 and down: 
    Please Login or Register  to view this content.
    This is the formula for filling in names in the top template (in C2, copied across and down): 
    Please Login or Register  to view this content.
    rotation
    Mon
    Tue
    Wed
    Thu
    Fri
    Sat
    Sun
    OFF
    Emp 01
    Emp 02
    weeks -->
    3
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    		 week 1
    OFF
    Sun
    6
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    		 week 2
    Wed
    Tue
    1
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    		 week 3
    Sat
    Fri
    4
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    		 week 4
    Mon
    OFF
    7
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    		 week 5
    Thu
    Wed
    2
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    		 week 6
    Sun
    Sat
    5
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    		 week 7
    Tue
    Mon
    0
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    		 week 8
    Fri
    Thu
    Schedule
    Mon
    Tue
    Wed
    Thu
    Fri
    Sat
    Sun
    OFF
    week 1
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    week 2
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    week 3
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    week 4
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    week 5
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    week 6
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    week 7
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    week 8
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    Repeat--> week 1
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    week 2
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    Emp 07
    Emp 06
    Emp 05
    Emp 04
    week 3
    Emp 06
    Emp 05
    Emp 04
    Emp 03
    Emp 02
    Emp 01
    Emp 08
    Emp 07


    ...and here's the formula used to fill in weeks on the actual Schedule (in C13, copied across and down): 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by leelnich; 10-27-2017 at 01:49 AM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
    Reply With QuoteRegister To Reply
  5. 10-27-2017, 01:55 AM #5
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Daily Schedule for 8 Employees

    Hi all- just updated post #4. New attachment uses new layout plus named ranges to simplify formula application and maintenance.
    Last edited by leelnich; 10-27-2017 at 01:57 AM.
    Reply With QuoteRegister To Reply
  6. 10-28-2017, 09:40 PM #6
    Rigged
    Rigged is offline
    Registered User
    Join Date
    09-17-2012
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Daily Schedule for 8 Employees

    Thank you leelnich!

    That worked great. I had to make a few changes to list the days in one column but it definitely fit the bill. I appreciate the help!
    Reply With QuoteRegister To Reply
  7. 10-28-2017, 09:46 PM #7
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Daily Schedule for 8 Employees

    You''re most welcome, glad I could help! And thank you for the rep. - Lee
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How can I use Solver to schedule my employees (Healthcare)?
    By lsm33000 in forum Excel General
    Replies: 11
    Last Post: 11-18-2020, 07:11 PM
  2. Number of employees on schedule per hour
    By bensorensen1019 in forum Excel General
    Replies: 1
    Last Post: 04-12-2017, 01:15 PM
  3. Automate Monthly schedule to Daily Schedule
    By Frytoos in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-03-2014, 09:09 AM
  4. Group Employees to a shift schedule
    By aka 42 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2013, 10:25 PM
  5. create daily schedule using master task schedule
    By DCO in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2012, 01:08 PM
  6. [SOLVED] Employees shift schedule
    By rossi in forum Excel General
    Replies: 1
    Last Post: 06-04-2006, 07:15 PM
  7. [SOLVED] How can i set up work schedule for 5 employees
    By help me in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-10-2005, 05:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1