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