Automatic shift scheduler

**carblo** · 02-18-2019, 01:48 PM

Hi

I want to create an automatic shift scheduler in Excel. I've included an example sheet. Basically I would like to create a table each month to schedule shifts for the employees. For each day there is a required number of employees (usually 4 but it can vary) and a maximum number of shifts for each employee per month. Also included in the table are the days certain employees aren't able to work (marked with an "X"). What I would need is Excel to fill out the table based on that criteria. If there are not enough employees with enough shifts to fill out the month, then I'd like it to just leave those cells empty.

Any help would be appreciated

Thank you!

**AliGW** · 02-18-2019, 02:00 PM

Welcome to the forum!

I think you are going to need VBA for this - would you like me to move the thread for you?

**carblo** · 02-18-2019, 02:01 PM

That would be great, thanks!

**AliGW** · 02-18-2019, 02:06 PM

All done.

**banaanas** · 02-20-2019, 01:14 PM

Ah, this was a fun project

See my attachment for a practical workbook.

I really tried to create it to be as flexible as possible. You can add more employees, or days and the code will work.
You can also manually add workdays, and the code will fill out the rest.

There are a few things that need to stay the same:

1. The first employer must be on row 2
2. No values of the cells that have a Red background can be changed (i search for them in the code, this is what makes the rest so flexible)

Here is the raw code if someone is interested:

Please Login or Register  to view this content.

**carblo** · 02-21-2019, 06:32 PM

Wow! You're absolutely amazing! I probably could have done it with a set table (with a bit of knowledge from Python) but this is flexible as well, which makes it just perfect.

I feel like you've done so much for me but I have to ask - do you think there could be a way that I could make it fill the days randomly, so people don't get 2 days in a row (if random sticks a person 2 days in a row that would be fine)?

I can't thank you enough again!

**banaanas** · 02-22-2019, 12:28 AM

Sure I can try to modify it that way.
Do you wan't it to be fully random, or do you only wan't to prevent that the same person doesn't get 2 days in a row?
Is there any other limitations that you want to add?

**carblo** · 02-22-2019, 12:26 PM

Fully random would be great. I think that would just put the icing on the cake.

**Schedulehelp** · 02-22-2019, 08:48 PM

[QUOTE=banaanas;5069337]Ah, this was a fun project

See my attachment for a practical workbook.

I really tried to create it to be as flexible as possible. You can add more employees, or days and the code will work.
You can also manually add workdays, and the code will fill out the rest.

There are a few things that need to stay the same:

1. The first employer must be on row 2
2. No values of the cells that have a Red background can be changed (i search for them in the code, this is what makes the rest so flexible)

Here is the raw code if someone is interested:

Please Login or Register  to view this content.

[/

Hello. I am unable to see the code. I am trying to make a random schedule for 14 staff members to work a total of 13 shifts a month with all weekends off for everyone. Each employee works 3 shifts a week and one 4 shift week. I need to have 8 people working M,W,Th and F. Tuesday I need 10. Is this possible?

**banaanas** · 02-24-2019, 04:47 AM

Alright see attachment for an entirely random solution.

It got a bit messy, but the basic principle is this:
Store all days in a collection. Create an array with randomised numbers (1 to count of days) use a loop to select dates randomly using this array's values.

Please Login or Register  to view this content.

**banaanas** · 02-24-2019, 04:48 AM

Hello. I am unable to see the code. I am trying to make a random schedule for 14 staff members to work a total of 13 shifts a month with all weekends off for everyone. Each employee works 3 shifts a week and one 4 shift week. I need to have 8 people working M,W,Th and F. Tuesday I need 10. Is this possible?

I am sure it is, but if you have trouble please open a new thread with your problems.

**carblo** · 02-26-2019, 04:00 AM

My dude, you're absolutely incredible. Thank you so much for helping me out. I can't thank you enough! This is perfect!

Kiitos paljon!

**Brandon22** · 03-31-2021, 10:44 AM

Hello. This solution works very well. I was wondering if there was a way to instead of putting a W on the day but to instead put a start and end time of work based on what that employee should be working that day?

**AliGW** · 03-31-2021, 10:45 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

**ricbrear** · 04-28-2021, 10:42 AM

Hi Bannanas:

I'm looking for something similar to Generate a the Schedule for our Church, Basically we have 30+ Volunteers that serve on Average 4 Services a Week, however, the biggest Issues is they all have different availability as to the days they can serve so it would be great for the Shift Scheduler to consider rules as to what days of the week they can serve.

**FDibbins** · 04-29-2021, 01:12 AM

Originally Posted by ricbrear

Hi Bannanas:

I'm looking for something similar to Generate a the Schedule for our Church, Basically we have 30+ Volunteers that serve on Average 4 Services a Week, however, the biggest Issues is they all have different availability as to the days they can serve so it would be great for the Shift Scheduler to consider rules as to what days of the week they can serve.

Did you perhaps miss the post IMMEDITELY above yours?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Automatic shift scheduler

LinkBack

Thread Tools

Rate This Thread

Display

Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Re: Automatic shift scheduler

Thread Information

Users Browsing this Thread

Similar Threads

3 Questions on the "Employee Shift Scheduler" Template

Shift Scheduler problem

Creating an automatic shift schedule within an all ready created shift schedule?

Running Excel from task scheduler. Scheduler doesn't end

Automatic Scheduler

Want to make automatic scheduler

Shift cell focus automatic

Bookmarks

Bookmarks

Posting Permissions