I'm going to show you how to create a
vacation or an attendance schedule for
your staff so let me show you some of
the features here so let's say you have
let's go back to January for starters
from the drop-down list I have all the
months so they're there you can see in
January and I have an employee 1 2 3 4
here but you can have as many as you
like
so sick days are indicated by an S
vacation days are indicated by a V and
we have the totals there so let's say
they get 15 vacation days in a year so
you know this this guys have used up for
and he probably gets half a dozen sick
days a day a year and you get he's used
up to so that when he when he wants to
go look at for example September then
you know that he has he's got eleven
days he could use up in the next in the
next while so let's go back to January
and and then we can do it so let me show
you how I did this and I I will show you
how to do it for one month I did it for
the whole year and you guys are welcome
to this file it's not complete but it's
it's a lot of the grunt work is done but
I'll show you how I did it for one month
so you get the idea so you can create
your own if you like so let's start on
sheet 2 and I'm going to start in b1 and
I'm gonna put in for the beginning of
this year so I'm going to go with zero
one zero one two zero one seven okay I
know from the fact I just did it that we
need to select columns D - aah and what
we're gonna do is we're gonna right
click on those and we're gonna change
the column width to three and say okay
and now what we're gonna do is we're
gonna put in the dates so what we're
gonna do is we're gonna go equals and
then we're going to select our our first
date and now that's not going to fit in
there so what we're gonna do is we're
gonna right click on that and we're
gonna go to format cells
we're gonna go to number and we're gonna
go to custom so right now I have month
day year so I would just like to have
day so I'm gonna put a D in here and so
it's just going to give me the one which
is perfect and now what I'm gonna do is
I'm going to go equals and I'm going to
select that cell and I'm gonna add one
and so I'm gonna drag this down or at
this across until I reach thirty-one all
right
and now what we're gonna do is we're
going going to we can probably put boxes
around this to make a look fancy okay
and so now what we're gonna do is we are
going to figure out what the weekday is
so we know which we which days of the
week or weeks are Saturday and Sunday so
what we're gonna do is we're gonna go
equals weekday and we're gonna do
weekday of the cell below now one is is
Sunday and seven is a Saturday so let's
just scroll right along here and so you
can see that the weekends are are
indicated there so now what we're going
to do is we are going to select this is
the bunch of cells below there and what
we're gonna do is we are gonna go
conditionally format them
so on main page we go to conditional
formatting and we'll go new rule and
what we're gonna do is we are going to
use a formula to determine the rule and
so now what we're gonna do is we're
gonna clip this little red arrow here
and what we're gonna do is we're going
to basing on these values so the I'm
going to do it the first time so if
equals if it equals seven now big trick
in this it automatically puts
conditional formatting on here so what
we're going to do is we're going to take
off the dollar sign before the D and the
dollar sign before the aah if you don't
do that it won't work and so now we'll
go back to the the rest of the dialog
box and we're going to clean up click on
the format button and I like the third
one down here I'm in the fill box I like
the third one down for the color and I'm
going to say ok and ok again and so now
all the days that are seven which is the
Saturday are greyed out and I will do
the exact same thing for the one so
there you have the the ones or the
Saturday and the Sundays are grayed out
here now in the a column is where you
would put the employee names so maybe we
can put employee names here
the next two I'm gonna I'm gonna want
the totals for vacation and the total
for sick days so what I'm gonna do is
I'm gonna go and then I'm gonna go sick
and so what I'm gonna do is I'm going to
format these I'm going to right click
and I'm going to format cells and I'm
going to go with alignment and I'm gonna
go this way okay and then I can make
these narrower and it really doesn't
matter that you could don't see the date
up here so what I can do is I'll go to
that and select it and then I'll make it
white to hide it because nobody really
needs to know so now what I'm gonna do
is I'm going to select this this group
of cells and I'm going to say that if
it's a it's a V then we're going to
format it green so we're back going back
to conditional formatting and we're
gonna highlight cells and equal to and
so let's go with the V and I want V to
be green okay and then we'll say okay
and then we're going to do the same
thing and we'll go for we will go for an
s to be sick you know I don't want my
sick days to be red with the red like
dark red letters and we're light red
fill and so we'll say okay now how we're
going to calculate this is we are going
to use a count if so you'll go equals
count if really have to learn how to
type and so what we're going to do is so
we're going to select all the cells in
here
and we're gonna say say count if let's
go to the thing to make sure I'm getting
it right and then we'll say if it equals
V and we'll say okay okay so now check
whether it's working let's put a V in
here and let's put a V in there okay and
let's do this up the same thing for for
sick days as well so now let me try to
see whether the sick days work out and
so we'll put a sick day in here and a
sick day in here
and so they are the totals now to bring
it down all you need to do is just drag
it down grab the fill handle and drag it
down so there you go so now how I
divided my month months on the where I
had the year is what I did is I selected
the entire month the B column to the a H
column and then when I went just to the
name box and I named this January so
there I'm going back to the other the
other sheet so what I have how I named
each one of these is I I selected them
the columns and then I will I called
them January so that way if you want to
go to March just a matter if for the
name box brings you there so that's how
I made attendance or vacation scheduler
like I say you can email me and I will
send you the file that I have done so
far you're welcome to it but it's pretty
easy to do please just please subscribe
thank you
