Automatically create shift schedule in Excel - YouTube

so it's always asking me the other day whether I could automate entering shifts

for employees into a work schedule now this isn't exactly what he had in mind

but it gave me some ideas on doing this so what we have is we have 10 employees

working at this corporation and they receipt they work five days straight and

get two days off consecutively and so what I've done is I've created something

so that it will put in five days on and two days off for every employee and and

once again they they have ten employees but they have they need they need six so

on the bottom this is my same shift schedule that I've showed you how to

create before so this is for the month of May and they need six people working

at every time every day so let's run this the days off are indicated with an

O and conditionally formatted so if they're older green so that they stand

out a little bit and so there you have the complete shift shift schedule for

them and you can see the number of people working and the all of these are

over six so when it says negative two that means that they have two people

extra there now so let's undo this so let's just delete this shift schedule

and then this is what I'm thinking would be an advantage to be able to run this

so let's say employee number one wanted vacation days 11th 12th 13th so let's

enter a vacation and I've color-coded or conditionally formatted that so it turns

yellow and so that's so we've entered the vacation now let's run the I've set

up a I've set up a box a button to run my schedule here and I'm just not going

off and running it so so let's run this again and then you see

that when you run you run a schedule with the vacation in there on on the

fifth your I'm sorry on the 11th you have a problem there's only going to be

five and so you're gonna be short one but on the twelfth and thirteenth it's

going to be no problem so maybe you could talk this person into possibly

taking their vacation the twelve thirteenth and fourteenth and that way

you would have no problem so let's change this to an X and let's change

this to a V so if this person we were able to change his vacation of the 12th

13th and 14th this would allow your schedule to work

perfectly because there's no days that you're short of anything here so let me

show you the code so basically what I used is I used the offset function so I

figured out the first the first row I equals four equals the fourth row is

where the employee goes and so I say range I 3 and I 4 is off and then the

next five days so I shifted I shifted by 7 and then I

put the other the next two days off and I keep going shifting by 7 and doing

those all the way down now I do a lot of copying and pasting on this little bit

of code here so I copied the whole works from I equals 4 2 equals 5 and now what

I have done is I've shifted that over by one for the FIR I've offset it by one

for the first row and then I've added offset to instead of 7 14 21 I have 8 15

22 and 29 to figure it figure out the rest of them so I've done that for all

of the 10 rows and then what I've done is I've gone through the range from c4

which is for two AG 13 which is c4 here to AG 13

and then I have I have said range C for Ag 13 dot replace what and I have what

is as nothing and if there's nothing there replace it with an X for working

so that is how you enter a schedule for your employees automatically I will

include this VBA code in my description please subscribe