- [Instructor] Hi, and welcome
back to myexcelonline.com. Today, we are going to show you one of our freebie templates, how to keep track of
yearly employee leave. So here's what our spreadsheet
is going to look like when we're done. We're going to have the
dates across the top for the whole year, and then
we're gonna have each one of our employees and how
many instances they have of annual leave, sick
leave, and unpaid leave. Also, we will have some additional
training options for you at the end of this video,
so stick around for that. So feel free to follow the
link in our description below on YouTube and get your
own copy of this template to use throughout this demonstration. So first, let's start
with column A right here, and we're just going to
label this column Employee and put in the name of
all of our employees. Now for columns B, C, and D, let's think about what kinda leave we would like to track right here. So let's say we would
like to do annual leave and let's call this A, and
then let's do sick leave and we'll call that S. And then in column D,
we will do unpaid leave and we'll call that U. So I'm just gonna highlight these columns, double-click here in order to auto-size, so there's enough space here. And then I'm going to
highlight these three columns. Let's center them, just like
we have employees centered. And then for the fill, let's just use one of these lighter blue colors. So starting in column E,
let's just put 1/1, 1/2 in F, 1/3 in G, 1/4 in H, etc. And then I'm just going to highlight this, and you can see on the
bottom right-hand corner, this square, and I'm just
going to click on that and drag out seven. So I get one week's worth of data here. And now, instead of having
the entire date formatted in row three, I'm going to right-click and go to format cells and
go down here to custom. And right here under Type,
I'm going to just type in a D for day and say, okay, and then I'm also going
to center those cells. So now I will be getting
the day of the month showing up right here. And now I can see that row two is hidden. I'm just going to click
here when I have an up arrow and a down arrow and drag
down and display row two, because right here in E2, I wanna type =weekday
with a left parenthesis. And I wanna use this function
to determine what the weekday of the number in the cell below it is. So I'm just going to click
the down arrow to select E3 and close my parentheses and hit enter. And I can see that I'm
getting a 2 for 1,1. And that's true, because January
1st, 2024 was on a Monday. And a Monday gets the weekday value of two because it starts with Sunday. So Sunday is a one, Monday is a two, Tuesday is a three, and so forth. So again, I'm just going
to click this square at the bottom right-hand corner and drag over to the right. And you can see that the weekday has automatically been filled in. So the next thing I would like to do is get the month up here, because I am hoping to do
this for the entire year. So if I click an E1, I'm going
to type = and click on E3 because there is already a
date in there and hit enter. And again, I'm going to click on that cell and click on the bottom
right-hand square right here and drag all the way to the right. And this time I'm going to right-click and go to Format Cells. Under Custom, instead of D, I'm going to do M, M, M and say, okay, and I'm going to center that as well. So I can see that I'm dealing
with the month of January from days one to seven so far. If you are liking this video,
please give us a thumbs up and subscribe to our channel and hit the bell button to get notified when we release our weekly videos. So next, what I would like to do is based on row two right here. If I have a seven or a one, I would like to make this
column a different color because a seven represents Saturday and a one represents Sunday. And I would like to highlight my weekends. So if I highlight this section right here, because this is where
all my employees are, and I go up to Conditional Formatting and I go to New Rule, I'm going to select this option down here, use a formula to determine
which cells to format, and then I'm going to click
this up arrow right here so that I can select E2 all
the way over to my last date. And then I'm going to
click this button again. And I want this if it's equal to one. So this is for every Sunday
because Sunday's equal to one. So if I click Format, I can pick a color for the background of my cells. So I'm just going to pick
this second color down here and say, okay. And then I'm going to say, okay, again, but nothing's happened. And that's because I'm going to go up to Conditional Formatting
and go to Manage My Rules and I'm going to click this
one and go to Edit Rule. And what I need to do is take off these absolute reference dollar signs
in front of the E and the K, and then I'm going to say,
okay, and say, okay, again. And now I can see that my
Sunday is being highlighted. But remember, I also want my
Saturday to be highlighted. So if I click on Conditional Formatting and go to Manage Rules,
I'm just going to highlight this rule and do Duplicate Rule, and then I'm going to hit Edit Rule. And instead of being one,
I'm just going to backspace and say seven for the
Saturday and say, okay. And then I'm going to say, okay, here. And now I can see that my weekends are in fact being highlighted. So next, what I would like to do is I would just like to
copy all of the formatting that I have done so far
to the end of my sheet, far enough out on my sheet where I'll get to the end of the year. So if I highlight all of this, and I can see at the
bottom right-hand corner, my square here. I'm just going to drag
this out for a while, probably somewhere where
the columns start with an N. And that's gonna take me to
about my 365 days for the year. And here is column NF where
I'm planning on stopping. So I'm going to let go of my mouse button and I can now see that my
weekends are being highlighted and I have everything
going out to December 31st. So if I click here and I hold down Control and I hit my left arrow, I
will go back to the beginning of my spreadsheet. So now I'm gonna highlight
row two, right-click, and go to Hide, because I don't
really need to see the day of the week anymore,
because I'm only using that for my conditional formatting. So now that I have my
whole calendar in here, for the annual leave, I'm going to make a COUNTIF statement. So I'm going to click here and go =COUNTIF with a left parenthesis. So what range am I going to count? Well, I'm going to count this here. And instead of going to L4, I now know that I wanna go out to NF4. So I'm just going to change the L to NF, and then I'm going to type a comma, and then my criteria
here is I wanna add up everything that has an A. So I'm going to put double quotes, "A", and close my parenthesis, and hit Enter. And for now I'm getting a zero. But I'm going to click back in this cell and highlight this formula right here and hit Control + C to copy
it, and then hit Enter. And then I'm going to
go into my sick leave for my first employee, and
I'm going to click up here and hit Control + V to paste, because I want the exact same
formula, except in this case, instead of an A, I want an S,
and I'm going to hit Enter. And then one more time,
I'm going to click in D4, click up here in the formula
bar, hit Control + V to paste, and change my A to a U, and hit Enter. So now I'm adding up all my A's
for annual leave, all my S's for sick leave, and all
my U's for unpaid leave. So let's just test this out. I'm going to put two U's here, and I can see those
are counting correctly. Now if I change my U's to S's, I can see those are
being added up correctly, and then I change my S's to A's, I can see that those
are adding up correctly. So my formulas over here are correct. So I'm going to highlight
these three cells where I put my formula. I'm going to center that count. And then over here on the
bottom right-hand corner, I'm going to click and
drag my formula all the way for all of my employees. And then I'm going to get rid of these A's because that's just test data over here. So one final thing that I might wanna do, if I am scrolling to the right, I can no longer see the
names of my employees. So let's scroll left and fix that. I'm going to highlight column
E and go up here to View and go over here to Freeze Panes. And I'm going to click Freeze Panes. And now when I scroll, I can
see all of the dates coming up and my employee names are
staying locked down right here, as well as their totals. So you should be pretty good
to go now for keeping track of your employees and their leave throughout the entire year. Feel free to use our template or change it up to fit the
needs of your organization. If you would like any further
explanation on this template, you can visit our blog in the link below in the description on YouTube. And if you have any questions or comments, please leave them and
we'll get back to you. Thanks for watching and
see you again next time. If you want to learn more about
Microsoft Excel and Office, join our Academy online course and access more than 1000
video training tutorials so that you can advance your level and get the promotions,
pay raises, or new jobs. The link to join our Academy online course is in the description. - If you like this video,
subscribe to our YouTube channel, and if you're really serious about advancing your
Microsoft Excel skills, so you can stand out from the crowd and get the jobs, promotion,
and pay rises that you deserve, then click up here and join our
Academy online course today.
