How to make attendance chart for employees in Excel

How to make attendance chart for employees in Excel

Posted by


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

16 comments

  1. after doing the conditional formatting for 7, how did you make it the same thing for the 1, and highlight the 1, saturday and sunday. Is it a shortcut to do it ? Or do I have to redo the conditional formatting to get that?

  2. Been looking for instructions on how to use "s" and "v" key (conditioning) and couldn't find
    anywhere!! Thanks so much!!! I've subscribed!!! Brill vid!

  3. If an employee drops in September… How we maintain him in previous and later months?
    Is employees'list different in every month or same?

  4. Hello Ms. Barb Henderson, could you tell me how to separate the month because I watched your video and I followed you, but it doesn't work?

  5. I'm following your steps accurately to apply conditional formatting to block out non-working days; however, only the first row has the formatting apply even though I've selected the entire range of cells for the month. Any ideas on why this is the case?

  6. Barb,

    Good video–I struggled, though I eventually figured it out. I'm one of those very highly and dependent kinesthetic and visual learners @ around 8:30 trying to replicate your "count if" function for totals and still wasn't sure how you were doing what you were doing beyond the typing. With the range, I had initial trouble highlighting my preferred rows and then making the dashed lines around them become solid. Then I got very frustrated in making the criteria function work–I could never get the word "criteria" to click ("range" would still be highlighted). I finally had to do what you did and click the function button to get it to pull up)

    So far, your video is the best I've seen, esp. in solving my problem in having my total or sum column by names rather than the very end (I tried AutoSum and the sum function without success)–I'm tracking student absences ("X" for absence, blank if here) for the semester and the rows get VERY long. Easier to see the total next to the student's name for final grading rather than horizontally scroooooolling way over to the opposite end of the row (AutoSum puts it here at the opposite end very inconveniently).

    I figured this out via another YouTube video from long ago but forgot and wasn't able to figure it again, now–so I'll try to remember this and hope your video sticks around. Thank you!

  7. I just created my January tracker. How do I go about creating all the other months? Do I have to do the same thing for each month?

Leave a Reply

Your email address will not be published. Required fields are marked *