I was asked to show how to create a leave tracker first for employment
employees leave for the entire year so this is how I start the first of all is
I have in column a I go down a few goals and I start with the employees and leave
enough space you can provide the employee names in here
and then in column B C a B I do sick CID vacation and D I do blank and then
in the first in the first cell next to blank here is I put in January 1st 2019
because we're doing it for this year and then I drag these dates and you keep
dragging them until you reach the end of the year so I'll just pause it for a
second here so I drag I drag the dates till the end of the year and so I get 12
31 is column and E so now what we're going to do is we're going to select all
of these that we have highlighted and then what we're going to do is we are
going to change the formatting so what I'm going to do is I'm going to right
click and I'm going to select format cells and from the box I'm gonna um I'm
gonna go custom I'm gonna select custom and I'm gonna select D to just show me
the day of the week and then I say okay and so now I just have the day there we
need to select all of the columns from any back to where we started with all
these columns selected I'm going to right click and select a column with the
four I'm sorry three and I say okay now where all our columns are small
narrow there I right clicked and it in enter inserted a new row above my dates
here and now what I'm gonna do here is I'm going to go use the weekday function
what I'm gonna do is I'm going to select my FX key here and I'm going to select
weekday and in that function what I'm gonna do is I am going to select the
date or the date below let me say okay now in the Microsoft world Saturdays are
7 and Sundays are 1 so it's giving me the weekday so I'm going to drag that
down till the end of the end of the dates so with the weekdays calculated
here what we're going to do is we're going to select all the cells that were
the day so that they work on and what we're going to do is we're going to gray
out the the Saturdays and the Sundays so I will so with all these select now
we're going to go to the Home tab and we're going to go to conditional
formatting and we are going to select new rule and then we're going to select
use a formula to determine which cells format to format and then what we're
going to do is we are going to select the days that we've cat or the weekdays
that we've calculated so we're going to select from we're going to select from
here and they're not going to put a full colon and then I'm going to put an e is
the last one that we had for the whole year so I'm going to go an E
now we're going to take off the conditional formatting for for the e so
we'll take the dollar sign off of the e and we're going to take a dollar sign
off of the end so now what we're going to do is we're going to say if it equals
equals seven so that would be Saturday and then we're going to set the format
and we are going to go to fill and we're gonna select gray here so that all those
Saturdays will be grayed out and we will do the same thing for all of the Sundays
now with all our weekends marked off I normally just hide this row with the
weekdays on it so that we don't need it anymore so now what we do is we do for
the sick days you're going to count if count if and if asks for 6d for vacation
and B for bank so what you're going to do is go equals count if so we'll go to
the function key and we'll go count if and so then what we're gonna do is we're
gonna select the range and we're going to select from here and then we put a
full colon and then we go and E and E for and what we're doing is we're
checking for the letter S and then we're saying okay and then we can drag that
down and we're doing exactly the same thing for V and B and I will continue to
do that for you now I've got the countifs done for the sick vacation in
the bank and so just because we have so many things in here what I would do
normally is on like the first the first they have the second month these I would
go in this cell above here I would go equals and I would select this cell
that's above there or below there just so that we can get the month okay
and so now what we're gonna do is we're gonna right-click and we're gonna format
and instead of a custom day what I'm gonna do is I'm gonna do a custom month
mmm and so it's gonna give me the three
letters of the of the the name of the month and so it says January there and
so then maybe you could make this larger so it's easier to see and bold and what
you can use it you can extend it into the next cell now I could repeat that
for every one of them the other thing you would need to do is you would need
to select the cell here and you would go to the View command and you would go to
freeze panes and you would go to freeze panes and now you will be able to scroll
all the way across and once again I would go equals this date and press
enter and then you can format and select month month month so that's how you
create it so now for example if I put in six days here they will be counted up
the six days here it doesn't matter when I'm putting them in anywhere during the
year if I put in a bank day here it will change the totals on the sheet so that
is how you create yearly a yearly try leap leap tracker
for your employees you can clean this up with lines and shadows but that's the
basic principle please subscribe