I am going to show you how to create a timesheet in Excel here we have the finished products and I'm showing you what the
features are timesheet so we have the date started at April first and then the
days of the grayed out are the Saturday and Sundays and you noticed there were
31 days and yet it only goes to 30 days which shows that that there are the 31st
days the first May obviously if so let's change the date here let's change it from 4 to 2 and
there you go it knows what that it's this February and there's 29
days February so that the first and second would be March also the dates
that are grayed out changed with the change of the month so the first thing
that we're gonna do is select columns C to AH and we
are going to change the width of the columns so we're all right we selected
them directly and we're going to call in width and we're putting in three
ok so what we're going to do in A1 is put in name and below that in A2 we are going to put start date
put in Tom Smith and let us put in a start date of May first we are going to black out this column
We are black out from b to AJ so that we have some separation In here we are going to put the tasks that a lot of employers want you to
divide the time during the day so they can charge different rates for different
hours so let's type in tasks here
okay and then what were you doing here is we're going to have the date so what I am going to do is equal
then I am going to select our start date and obviously doesn't fit in there so what
I'm doing is right click and format the cells and then to select Custom and I'm
going to type in his day here so I am going to type D for day and say ok
So that gives you a 1
ok so now what we're going to do is to create the next day we will go this date plus one and so
then we have to do is grab the fill handle and we're going to drag down here
ok we can do here now is we can put some boxes around here so let's select this
first row here this border borders all borders ok so now there's your dates done for you and
so what we're going to do is we're going to go down to the bottom here and we're
going to select another role that's on the bottom is parallel to these and
we're going to put the same borders on this and this is where the hours are
where the hours are totalled gonna go and we're gonna press Enter
and then we're going across now what we're gonna do is we are going to put
boxes around here are you enter your hours and so we're select cells and
we are going to put boxes again so now I am going to figure out which days of this chart are weekends than need to be greyed out
I need to do is going to cell C2 here and then I am going to go equals weekday and select our
starting day and I'm going to put a ending bracket on and press enter so it's going
to be one and so to drag it across I am going to have to go equals weekday again and I am going to select
this one which is our first date and I'm gonna go plus one we'll go enter now
dragged out across
you can see that it has it goes to 7 1 being sunday and seven being saturday
now what we're gonna do so we're going to conditionally format these these days
these dates for the month so if the weekday is equal to one or seven we're
gonna change it to grey so we're going to select the days of the month we like
to format and then we're gonna go conditional formatting on the toolbar
here and select new rules and I'm use a formula to determine rule and I'm am going to click
little red arrow so we can selected the data that we want to check and here you have
the data here this tends to put absolute reference on these cells and we don't
want that so we don't want this so let's take the dollar sign out of the
C and the dollar sign out of the AG and then we go back we say these ones
equal one change the format and so what we're gonna do is click on the
format button we're going to go to the fill and we're gonna select the second
one from the bottom here and we're missing ok and we are going to say ok
so the days they are the sunday greyed out and we can do this
repeat the same operation for them for this Saturday so if it's a
one or a 7 down at the things you need to do is we're gonna selected the bottom
dates here and with this will be a simple simple conditional format will go
to conditional formatting and will go so rules that are greater than in
eight
ok so that means anything that's over eight hours will be highlighted so the
next thing is just some automatic sums so here so we're going to get our
automatic sum button and we're going to select from the first to the end of the
month and press enter and then we can drag that down and then we'll do a sum at the bottom of
the totals that we calculated already you can do is select these cells
and put squares on each one of them now we can put tasks here lets a task one and task 2 and
we'll put in a few hours here we go
5 6 3 so here you have the totals coming here for the task and then you have the
totals are over over eight hours changed to red so that you know that they are over time and so
besides I think we can do here is we don't really need these dates are up here
so when i'm new do is formatting
this is a trick I used to change the font color to white so that they
disappear so to finish this up all you do is formatting by putting the some
black underneath here and so on so that is your quick functional time sheet in
Excel