Automatically create shift schedule in Excel
Тәжірибелік нұсқаулар және стиль
Automatically create shift schedule in Excel. Enter work days and off days for each of your staff. VBA code included. Use the offset function. Use the search and replace function. Check out my templates page for free and purchased templates www.easyexcelanswers.com/temp...
www.easyexcelanswers.com/cour...
Contact me regarding customizing this template for your needs.
www.amazon.com/shop/barbhende...
How to insert VBA code in Excel • How to insert VBA code...
sub autoschedule ()
Dim i as integer
for i = 4 to 11
if i = 4 then
Worksheets("Sheet1").Range(Cells(i, 3), Cells(i, 4)).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 7).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 14).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 21).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 28).Value = "O"
End if
if i = 5 then
Worksheets("Sheet1").Range(Cells(i, 3), Cells(i, 4)).Offset(0, 1).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 8).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 15).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 22).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 29).Value = "O"
End if
if i = 6 then
Worksheets("Sheet1").Range(Cells(i, 3), Cells(i, 4)).Offset(0, 2).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 9).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 16).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 23).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 30).Value = "O"
End if
if i = 7 then
Worksheets("Sheet1").Range(Cells(i, 3), Cells(i, 4)).Offset(0, 3).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 10).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 17).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 24).Value = "O"
End if
if i = 8 then
Worksheets("Sheet1").Range(Cells(i, 3), Cells(i, 4)).Offset(0, 4).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 11).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 18).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 25).Value = "O"
End if
if i = 9 then
Worksheets("Sheet1").Range(Cells(i, 3), Cells(i, 4)).Offset(0, 5).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 12).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 19).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 26).Value = "O"
End if
if i = 10 then
Worksheets("Sheet1").Range(Cells(i, 3), Cells(i, 4)).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 7).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 14).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 21).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 28).Value = "O"
End if
if i = 11 then
Worksheets("Sheet1").Range(Cells(i, 3), Cells(i, 4)).Offset(0, 1).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 8).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 15).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 22).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 29).Value = "O"
End if
if i = 12 then
Worksheets("Sheet1").Range(Cells(i, 3), Cells(i, 4)).Offset(0, 2).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 9).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 16).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 23).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 30).Value = "O"
End if
if i = 13 then
Worksheets("Sheet1").Range(Cells(i, 3), Cells(i, 4)).Offset(0, 3).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 10).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 17).Value = "O"
Range(Cells(i, 3), Cells(i, 4)).Offset(0, 24).Value = "O"
End if
Range("c4:AG13").Replace What:="", Replacement:="X"
End Sub
For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
Excel one-on-one on-line training available. Email me to arrange.
I am able to provide online help on your computer at a reasonable rate.
Check out my next one-hour Excel Webinar
www.crowdcast.io/easyexcelans...
Check out Crowdcast for creating your webinars
app.linkmink.com/a/crowdcast/83
If you need to buy Office 2019 follow
amzn.to/2VX5dv8
I use Tube Buddy to help promote my videos
Check them out
www.Tubebuddy.com/easyexcelan...
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
kzread.info_cs_p...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
Пікірлер: 131
Thank so much for watching my video. Please note, I am not allowed to include square brackets the description of the video, so that if you copy the code from the description, I have replaced >, , and Before using the code check for these instances.
@syafieeanis6232
3 жыл бұрын
Hi, I would like to suggest to put your code into Google Doc and share the link in the KZread description. That's more better for us. Thanks!
@foot.balljerseys9097
2 жыл бұрын
Yeah cause it is showing some error
@darryldelasala8736
2 жыл бұрын
Can you send the excel sheet with codes? 😀
@BarbHendersonconsulting
2 жыл бұрын
@@darryldelasala8736 the code is located in the description of the video
@safetyfirst1374
Жыл бұрын
Wow Barb that’s awesome can you send me this formula to my email?
Hi Barb, thank you for sharing this. We always need more people like you sharing your theories as it really helps us all learn. There are other ways of doing this, but the main thing is to share and collaborate. That way we learn and adapt. Nice one Barb!
@BarbHendersonconsulting
Жыл бұрын
You are so welcome
Great. Now I know more about scheduling shifts and thus I can work more efficiently, effectively, and maximize my income.
@BarbHendersonconsulting
Жыл бұрын
Great to hear!
THANKYOU SO MUCH!!!
@BarbHendersonconsulting
Ай бұрын
You are so welcome!
@maghfuriadhi
Ай бұрын
@@BarbHendersonconsulting Your Excel tutorial content really makes it easier for me to complete my work. Your channel is obviously deserve more and you'll make it there! keep on creating! and wish you luck! Love and peace from Indonesia, God Bless You!
Do you have a video on how you created the macro you are running with that "button"?
@BarbHendersonconsulting
5 жыл бұрын
The code is included in the description of the video
Hi im trying key in my work schedule in excel. How to i auto reapeat my shift pattern with out keying in my shift one by one? So for example in a 2 week period my shift starting on monday to the following sunday would be as such D D O R N N N O O D D O O R D=day N=night O=OFF R=Rest So how do i repeat this pattern for a year?
Hello Barb, The video was very helpful. Can you please guide as to how to incorporate the codes in the excel sheet. Is it like a formula that we place in every cell? I am unable to grasp this at my current excel proficiency level!! Thank you.
@BarbHendersonconsulting
4 жыл бұрын
The answer to that question is too long for me to write here. I am teaching an introduction to VBA in a one-hour webinar on November 19 th www.crowdcast.io/e/introduction-to-vba
Amazing
@BarbHendersonconsulting
Жыл бұрын
Thanks
Awesome
@BarbHendersonconsulting
3 жыл бұрын
thanks
@Barb looking to see if you can help me on an excel problem where I am trying based on intervals needs, create number of shifts that employees can select (accept).
@BarbHendersonconsulting
3 жыл бұрын
EMAIL me at easyexcelanswers@gmail.com so I can understand
hi barb, i would like to know if there is a formula for example, employee 1 only works 4x a week then employee 2 works 3 days then employee 3 workds only 2 days. would there be a formula to automate something like this?
@BarbHendersonconsulting
4 жыл бұрын
no there is no formula, you would have to customize this schedule.
Hello, Can you please share the excel sheet of the same. Nice work and it very useful.
@BarbHendersonconsulting
4 жыл бұрын
The code is in the description of the video
Hi, can you create a 5 person roster with 2 persons for resilience?
@BarbHendersonconsulting
4 жыл бұрын
just reduce the size of the list
Hey, thanks for the video. I have 7 members in my team and it will be 24/7 with 5 days on and 2days off. Can you help me with this? How do I automate this
@BarbHendersonconsulting
4 жыл бұрын
email me at easyexcelanswers@gmail.com and we can discuss
@DayaChowdry
2 жыл бұрын
If you were able to make it, can you share it with me too.
18-Week 12 Hour Shift Rotation between 4 Teams Repeating Format: 1st Week - MonTueoffoffFriSatSun 2nd Week - offoffWedThuroffoffoff Day Shift: 6am-6pm Weeks 1-18 PM Swing: 9am-9pm Wks 1-6 / 12pm-12am Wks 7-12 / 3pm-3am Wks 13-18 Night Shift: 6pm-6am Weeks 1-18 AM Swing: 9pm-9am Wks 1-6 / 12am-12pm Wks 7-12 / 3am-3pm Wks 13-18
@BarbHendersonconsulting
Жыл бұрын
If you want custom work email me at easyexcelanswers@gmail.com
dear canu make schedule for staf to working three sheeft with one day off for every one and it should be three is one superveso in every sheft and in everysheft should be some one have a csr to drop the ppl i tree to do it for three months kindly help me
@BarbHendersonconsulting
5 жыл бұрын
email me at easyexcelanswers@gmail.com
Hi Barb. Could you share how I can create this auto roster generator from the scratch?
@BarbHendersonconsulting
Жыл бұрын
I have pieces of that this is how I format the sheet kzread.info/dash/bejne/lpiux5VtYNS2cbQ.html and this is how you insert the code I provided in the work book kzread.info/dash/bejne/c3atqKpphLGbY7A.html
Do you charge for writing code for schedules? I have watched this video and have made a schedule that I would like automated.
@BarbHendersonconsulting
4 жыл бұрын
Yes, I charge for coding schedules. This is what I do for a living.
@anthonymorrow610
4 жыл бұрын
@@BarbHendersonconsulting you're a wizard!! I'm learning about Linear Programming in college now. This is so cool
Hi, how do I create a scheduel that has shifting days every week and one 3 day weekend every month. For example, Monday Tuesday off this week, the following week Tuesday Wednesday, the next week Wednesday Thursday, next Thursday Friday, then the following weekend would be Friday Saturday Sunday. Now that the 3 day weekend came the next days off are Saturday Sunday to begin the rotation again followed by Sunday Monday and so on untill the next 3 day again. I hope this helps. I would greatly appreciate any help.
@BarbHendersonconsulting
Жыл бұрын
You would have to set up a month and go six and two for the first three weeks and six and three for the fourth week. You would have to define your weeks in a month as first, second , third and fourth. You could define your weeks by counting the for example Mondays, first week would be the first Monday, the second week would be Monday plus 7.
Is it possible to create a macro for shift work that has fluctuations of people required and random days off? I'm trying to create a rota single rest days are kept to a minimum, but my brain is struggling to do itself 😅
@BarbHendersonconsulting
2 жыл бұрын
That sounds like custom work. If you want me to work on it contact me at easyexcelanswers@gmail.com
In your shift schedule code. When you generate a schedule with week ends off "O". (Example 3/1/2022 the schedule works fine with the 7th and 8th day showing 'O" on Sat and Sun but when you change months. (Example) 5/1/2022 the code has "O" on 7th and 8th but that is Thu and Fri not Sat and Sun. Would you happen to have a quick fix for this.
@BarbHendersonconsulting
2 жыл бұрын
you need to clear the schedule, change the date and then run the schedule.
I looked at your excel files you've uploaded, but couldn't find a 3 employee schedule 4/10s and 7days a week. Can it be done?
@BarbHendersonconsulting
4 жыл бұрын
Just a matter of adjusting the code that I have included in the description of the video.
@colinbergstrom3955
4 жыл бұрын
@@BarbHendersonconsulting how do I get you to help?
@BarbHendersonconsulting
4 жыл бұрын
@@colinbergstrom3955 Book an online meeting with me and we can discuss what you need. Here is a link to my online calendar calendly.com/easyexcelanswers
Hi, can you help me make a employee duty roster in my feasibility study. It is a food truck and we operate from monday - sunday with only 4 positions (head chef, cook, manager, sevice window attendant/cashier )
@BarbHendersonconsulting
2 жыл бұрын
email me at easyexcelanswers@gmail.com
We been using this roster, it's been a formated in such a way that we become custom to it. Any other new roster template I see in KZread will only confuse me. So, the roster where in your template where you have employees names. Ours it's been set up from Night, PM, AM and Day shift which comprise of 4 rows with 3 person per shift. 3 per person it's been arrange column wise like the date on the top like for example 1st of January. Underneath it column wise the 3 employees names is been enter after the 3rd name, a row blank is left blank than row and press enter to move below is the PM Shift Than enter the next 3 names. Left a row blank than press enter to move below is the AM Row Shift So in the main body of the roster where you have the abbreviations of morning, night, PM or day. Our we have the name of the employees. I've been trying to auto populate the names according to the months as we move to the new month, the new names auto populate
@BarbHendersonconsulting
5 ай бұрын
I have no suggestions but wish you good luck
the script looks promising but can you share the link for step by step process please , thanks
@BarbHendersonconsulting
3 жыл бұрын
The code is provided for people who have some knowledge of VBA. If you need to learn VBA can I suggest my course. barb-s-school-c482.thinkific.com/courses/introduction-to-vba-for-excel
Hi mam , when i use your code, it throws a compile error.. For without next.. Pl .help
@BarbHendersonconsulting
4 жыл бұрын
Go through you code again, the code that I have in the description of the video runs. What have you changed?
@vincenzobuttiglione
3 жыл бұрын
May be useful to know... add a next before latest range instruction
I’m in process to make a schedule project for our department. I found ur videos amazing, and covering all points I’m looking for. Could we make online meeting and start the project together and make the deal for the cost and other related requirements. Thanks
@BarbHendersonconsulting
2 ай бұрын
email me at easyexcelanswers@gmail.com and we can set up something
@bbbbbbbb6966
2 ай бұрын
@@BarbHendersonconsulting email already sent. Thanks
How to create a work roster that is fair for each team member which doesn't take so long to work it out. Everyday there are 4 duties to be shared by a team of 4 people. The duties are changed changed after lunch. This applies from Monday to Friday. I cannot find an app that allows me to key in the four parameters for four people for am and pm shifts 5/7 so that each person gets an even work load throughout the week.
@BarbHendersonconsulting
3 жыл бұрын
This is too of a topic customized of a topic for me to make a video. If you would like to enquire about hiring me to complete. email me at easyexcelanswers@gmail.com
Can you customize it just for 2 employees working 5 days 2 off?
@BarbHendersonconsulting
3 жыл бұрын
you can easily customize the code
can you help me make a schedule like this?
@BarbHendersonconsulting
5 жыл бұрын
email me at easyexcelanswers@gmail.com
Barb - How difficult is it to create a 24/7 shift schedule with 2 staff needed per shift. 5 days on 2 days off. ThanksMark
@BarbHendersonconsulting
5 жыл бұрын
I have created similar, email me at easyexcelanswers@gmail.com
@jonathanmoore5942
3 жыл бұрын
Barb Henderson Hello Barb, I need a 4 day on 2 day off schedule rotation from today until year 2025. I oversee about 49 staff members. Can you help with this?
Is it possible to a FIFO (fly in, fly out) staff roster schedule with a staff being on site for 6 weeks and out for break for 4 weeks?
@BarbHendersonconsulting
3 жыл бұрын
This video has been used for this purpose kzread.info/dash/bejne/a4iOrqyJfsaweag.html
Please what is the rational behind the number needed?
@BarbHendersonconsulting
2 ай бұрын
many companies have a minimum number required for each shift.
can someone do it and upload it for download please :)
Are you able to help with only 9 staff working?
@BarbHendersonconsulting
Жыл бұрын
email me at easyexcelanswers@gmail.com Maybe I have a template that would work that I could sell you.
Hello, how can I do just a monthly weekend schedule for 13 total employees?
@BarbHendersonconsulting
Жыл бұрын
Check out this video kzread.info/dash/bejne/iaNl3MmEosKop84.html
hello, how about 6 worker 3 shift a day for 4 days on duty,and 4 days off duty,but shift keep rolling next on duty thanks
@BarbHendersonconsulting
5 ай бұрын
contact me at easyexcelanswers@gmail.com for custom work
Hi mam! How can I do this excel in my computer. We are understaff and i am suggesting to my head nurse 12 hrs duty. We are total 30 staff in a dept. Please help me to do a 12hr sched for 30 persons to prove to my boss. Thank you
@BarbHendersonconsulting
4 жыл бұрын
Have you seen this video kzread.info/dash/bejne/qJ-I1ciqj9LAZZs.html how to create 12-hr shifts
30 person 26 days working 8hours shift schedule
@BarbHendersonconsulting
10 ай бұрын
That is custom work, email me at easyexcelanswers@gmail.com if you want a price
How can i split for 3 shifts each contains 25% in 1shift, 65% in 2shift and rest 10% in 3shift with different week off
@BarbHendersonconsulting
3 ай бұрын
if you want custom work email me at easyexcelanswers@gmail.com
How to make shift schedule, time keeper for firedept, police, etc. A job application asked me I know time keeper and which software I use.
@BarbHendersonconsulting
10 ай бұрын
Excel is just the software that I am good at.
How did you put all "X" and "O"? I am confused. Please help 😊
@BarbHendersonconsulting
Жыл бұрын
The code is located in the description of the video and here is a link on how to insert VBA code in an Excel sheet kzread.info/dash/bejne/c3atqKpphLGbY7A.html
Can i do 12 hour shift schedule for 24hr. Using this?
@BarbHendersonconsulting
Жыл бұрын
check out this video kzread.info/dash/bejne/qJ-I1ciqj9LAZZs.html
Hi can you create a schedule from 5 am to 1am with 16 employees per day in a 8 hours shift
@BarbHendersonconsulting
Жыл бұрын
That sound sounds like custom work. email me at easyexcelanswers@gmail.com
Thanks Barb. If you have this code by python 3 ,I'll be thanks )) , I'd like to do app like this by python
@BarbHendersonconsulting
3 жыл бұрын
Sorry only in VBA
Barb, I over see a shift where we work 2 different shifts every other month with a team of 6. We do chores every night how can I set something up where I can rotate the chores evenly between 2 teams of 6 every week? 1 month we have 1 team that will cover Sunday thru Wednesday and if u work Sunday u don’t work Wednesday then the other team covers Thursday thru sat with only one person working Saturdays.the. Then the teams swap every month. So Monday thru Friday we have 4 chores that need done 1. Emails 2. Hot sheets/Toyo 3. Coding/filters 4. LTC.
@BarbHendersonconsulting
2 ай бұрын
This sounds like custom work. If you would like me to do custom work email me a easyexcelanswers@gmail.com
nice.. schedule.. can you make a schedule app that can accomodate 14 employee for hospital... our shift perday is 8-4 4-12 12-8.. each shift has 3 person 1 nurse and 2 nursing attendant we have 7 working days each person have a 2 days off.. ps. we have only 6 license nurse..
@BarbHendersonconsulting
3 жыл бұрын
If you would like to hire me to do custom work, easyexcelanswer@gmail.com
How can I download Codes
@BarbHendersonconsulting
4 жыл бұрын
The code is located in the description of the video
for without next error
@BarbHendersonconsulting
7 ай бұрын
check you code, mine works
Can you send me the code... email was sent to you mali
Plzz give Excell file of it
@BarbHendersonconsulting
4 жыл бұрын
The code is in the description of the video
Hello madam Can you send this sheet to me ?
@BarbHendersonconsulting
2 жыл бұрын
I do not send out sheets that contain code. The code is located in the description of the video
My number working and my number needs does not work
@BarbHendersonconsulting
Жыл бұрын
make sure that the line numbers match the number of workers
from your code occures a "for...next" error...????
@BarbHendersonconsulting
Жыл бұрын
Check that you have an end if and all your if statements
@georgestamatopoulos4886
Жыл бұрын
@@BarbHendersonconsulting I just copied yours
Can someone please share this over Google docs. Looking over the comments seems like we’re all struggling here. I’m not very proficient with excel..
@BarbHendersonconsulting
2 жыл бұрын
good luck
Pls don't show comments vedio not show
We have 8 oh hour 6 oof wate is that
@BarbHendersonconsulting
2 жыл бұрын
for custom schedules email me at easyexcelanswers@gmail.com
its a scam
@BarbHendersonconsulting
Жыл бұрын
How is this a scam?