I did some work a guy the other day and he had a schedule or he had specific
people working specific days of the week and he wanted to run a schedule so that
he could check for vacation than sick days to see if they had enough to cover
and so here I have for the month of June and I have the weekends grayed out and
how I did that is conditionally formatted this with these are the
weekdays I'm granule the days that are day 7 or
day 1 in the Microsoft world Saturday is a 7 and Sunday is a 1 and so I'm doing
conditional formatting and just grin out these cells so we know which ones are
the weekend and so what I'm doing is running schedule and he has a number of
people that work Monday to Friday and have the weekends off then he has a few
people that work Monday Wednesday and Friday and have the weekends off and
then he has a few people that work Thursday Friday Saturday Sunday now he
is required to have let's say he's required to have three people on a shift
at all times and so let's so for June let's run this schedule and so here we
go here's the first three and they have Saturday and Sunday off and they work
Monday Tuesday Wednesday Friday right and then here we have the second group
of people and they only work Monday Wednesday and Friday and then the third
group of people they work Thursday Friday Saturday Sunday now here you have
he needs to have three working and so everyone is here is good now he Housel
has another person so employee number 10 it's not scheduled he if he's short he
can call in someone to work on the odd day so let's for example say that this
person here wanted vacation and so let's let's do vacation for the last three
days before the weekend so D for vacation and so you can see that that
would be fine now if one of these other people
were it's closer wanted to have vacation right here so if they wanted to have
vacation here you can see that he's short by by one person so this is would
be the days that he would need to call in the the other person to fill in so
I'll show you how I did this so I've declared a couple of values I as
an integer Saturday Sunday Monday Wednesday as an integer and day value
was rain for what I've done I'm using this this column here which is telling
me the weekday the weekday of each of these and so what I'm doing is I'm
looking for I'm going through the cells from c22 to eye 12 and basically that's
the first seven days and I'm saying if cell value equals seven so if it's a
Saturday then we're gonna call the cell and cell column number is Saturday and
then what I'm doing is I'm looking for a Monday oh then this goes for for loop
and then next cell and then I'm looking for a Monday same thing if cell value
equals two then the Monday is the cell column number and carrying on and then
just an easy way to determine Sunday is Saturday plus one or Wednesday equals
Monday plus two and so then for for the for the three people that work Monday to
Friday all I'm saying is I'm saying that you find Saturday and Sunday and they
get those off and then you go off set it by seven 14 28 and 28 and they get every
weekend they get off and so then when you get to Monday Wednesday and Friday
that gets a little more complicated so once again they get Saturday and Sunday
off and then they get you fight you find Saturday and offset by three and that's
Tuesday they get off and the you offset by five and that's
Thursday that they get off and then that continues on for the rest and then for
the group that works Thursday Friday Saturday Sunday here I've got my notes
in here so I am selecting Monday to wednesday as off and then I just offset
this 7 14 21 28 and now what I've done is I've selected the whole range and we
replaced anything that doesn't have something in it without an off on and
I'm replacing with an X to be working and go so let's go back to our sheet and
let's just delete all of this and then let's let's change this to July so from
6:00 to 7:00 and then of course the day's will the weekends change and then
let's around the schedule again and there you see on a it go it follows
every month I will of course include the code in in the description of this video
please subscribe