I received this question today he was about my attendance sheet and this lady
would like to do totals and also have the days that their dates of their
absences and dates of their sake and one other type of leave they have what I
have done is I have worked that out and I'm going to show you how I did it we
have employee one here I've done this one and so you can see the six and the
vacation days that they have and basically you you could just put in any
letters and you want it to make it and make it color conditionally formatted
employee one has two sick days and seven seven vacation days so let's go to the
next sheet and it says employee one and has two sick days and so he's sick on
January 21st and February 11th and these are the vacation days that he's taken
already let you let me show you how I have done this on the first sheet what I
did first of all I think I might have changed my date a little bit here so
what I did is I put in the date and I formatted it so that only the format
cells and I did number and I did custom and I I just wanted the day so that's
what it is and then I dragged that down to get the
whole year here and then I won't go into how I did the weekends and everything
else because that's covered in my other video he put a blank cell in between
employee 2 and employee 3 so all hide this so the first thing I did is on the
second sheet I have total sick days and so I just did a direct link to it so
let's go to the second sheet or the first sheet and they have three sick
days so let's click on that and press ENTER and then for total vacation days
same thing I go equals and then we'll go back to the sheet one and get their
employee to vacation days and press enter
I am entered in a row between the two and the three employee to employee three
and what I'm going to do is I'm going to name this range and so I'm going to
select all the row all the way to the end of the year and I'm going to
this employee - just because it'll be easier than having to take this song
you'll be easier for the formulas later on so now let's go back to the beginning
and what we're gonna do is we are going to count the number of s's and the
number of V's in here so what I did in this role is I did a count count if and
then what I did is I selected the first cell what I did is I put the second cell
on there as well or the the same cell on twice but what I've done is I put the d7
in twice and now what I'm going to do is I'm gonna F for the first d7 so that it
stays the same and then what we're going to count is the number of times that the
value of d7 occurs and we'll go and close the bracket and press Enter so
right now we have 0 when we drag it across you'll see that it has one sick
day two sick days three sick days because the 3 s's have occurred and so
let's keep going and then it will start counting on the vacation days that's not
going to really help me for starters all these zeros don't help me much at all so
what I'm gonna do here is I'm going to go I'll start it with an if if d7 equals
nothing
then we're gonna put leave it blank and it contains something then we're gonna
count oh you better calm me here okay so now when we drag that across we get just
just the numbers with one the dates that have color in them this doesn't help me
much because I need to define which which ones are sick days in which days
or vacation days so what I'm going to do is I'm going to combine the sick days
and the number one the s mo and the one together and the SN the or the V and the
one together so what I'm gonna do is I'm going to go in my formula
I'm gonna go d7 and then I'm gonna go ampersand to end to add stuff and then
I'm going to put quotation marks and then I'm gonna put a dash and quotation
marks and then another ampersand to add it to this the other one and so now what
we're gonna get is we're going to get an s-1 and a v1 and all the way down it's a
little press ENTER so let's drag that across
so we get the s ones the s2 zs3 is the v1 v2 and v3 okay so now what we're
going to do is we're gonna go back to our c3 and we're gonna do an index match
here so we have three sick days here so we're looking for the date of the first
day sick day the data the second sick day in the date of the third sick date
so we'll go index match so equals index and what we're gonna do is we're gonna
index and I've named the dates so I'm gonna so here it is my name named range
dates and then I'm gonna put a comma and then I'm gonna put match okay so what I
am looking at looking for is its first sick day so I'm gonna go s - one
actually I should put quotation marks around that yes - one quotation marks
and I'm going to look in in the employee - range that I've made so I'm gonna go
employee and here is employee - okay and I would like an exact match so I'm gonna
put zero in here and I'm gonna close the brackets brackets two cloves and then
I'm gonna get a date that's similar to this one now you can go to format and
and and do that but or if you have a date that's already here what you can do
is go click on your date take the format painter and click on
your next date and you will have the date now so if we drag this down you'll
get the same date in here as right so now what we need to do is just change
this s1 to an s2 and press Enter and that will give you the second sick
day and if we drag this down again it will give you the third sick day
okay so let's just do it quick again for the vacation days so what we're going to
do is we're going to do we're gonna do equals index match index and so we're
gonna index the dates makes it easier when you name the ranges and put a comma
and then you do match and you go and so we're gonna look for a vacation day one
so it's going to be quotation marks the - one and both quotation marks and then
we are going to put employee - and of course we want a zero because what you
want an exact match and was closing the quotation marks and pressing enter take
the format painter and you paint your date to get the right date in there and
so then same thing you can change that to vacation day - and you will have your
to be your vacation days in for you so that's how you total up your your days
that are off on your calendar or on your schedule I would go back and hide this
because it doesn't need to be seen just you who happens to know oh now that this
is how you get all your totals and your dates for your staff please subscribe