Simple Excel Timesheet

In this simple tutorial we look at how to create a timesheet in excel. We'll be covering how to convert time values into decimal values, how to round numbers off using Excel's Round() function and how to use IF and OR statements to hide values when there are Blank Cells in our data.
www.SwanseaTechSupport.co.uk

---

Closed Caption:

hi this is Peter from Swansea tech
support . coat UK you're watching a
video on Exile timesheets to create a
simple time sheets or a core staff
clicking times
so first I thought throw together a
simple template that you can use the
core clock in times of your staff and
then we'll just tweak a few of those
formulas just kind of give it a more
professional touch the whole spreadsheet
but before we begin I noticed that when
i create this
it comes up with a few error messages
and you might experience so i just want
to show you how to turn off a lot of
those are necessary
error messages first so if you just come
into the file go to options and then
just go to the formula bar so you can
see here the only ones i actually have
checked on here this one and this one
all these are the ones I for music fair
to empty cells and things are that just
really don't matter to me at all
I'm these days are the only two which
are actually have errors in Louisiana
without you i'm showing so just make
sure that only those two are checked and
click ok on that one and then we're good
to go
so the format for our table is going to
be a column headings are going to be day
the time we clock in the time we start
break
okay well I just double click that want
to enlarge its time they finish break
ok the time that they clock out and now
we're going to have the total time and
then you know quite often you need to
input this data on another spread sheet
somewhere else or into you know a
database somewhere
and they often want you to have the
decimal values there rather than the
time value so i'll just have to
accelerate to convert those into
decimals for us as well
so the tape is just started a state
which is going to be 14 the March 22
hour
ok no just like many you cakes I've been
the 14 first
that's fine ok let's track these kind of
arbitrary number
just because i want to show you here on
the autofill options which you have on
Excel 2007 to excel 2010 you going to
come down to fill in the weekdays if you
only work monday to friday you can fill
that in and i also want to change this
for months on the air as well because i
want to show the what day of the week it
is alongside the date there
so constant number we're going to use a
custom number four month which is going
to be a day which is going to be a short
day as opposed to a long day
why out so they did yeah okay and that
will then give us that one if you want
to change it to the long dates just come
up here but an extra day and there and
that will then give you the long day
Monday Tuesday Wednesday
she was so much easier as you do it okay
there we go
okay enough enough laughing about let's
get on this time ok so we've got nine
o'clock that they talk and they
typically will take that break about 12
to about you know after now
about 12 30 degree clock out around five
o'clock so there's some more time is
going to be the time that they clock out
take away
deciding that they finish that break
that gives the afternoon session so that
we want to add the morning session which
is time they start break
what's no not equal sign on to go to
minus the time they clocked it
ok well just give us control and so I'm
not one will get a seven hours that's
fine i want to convert this one's with
that small so I'm gonna say equals that
one x by one day 24 hours so kind of the
world multiplication symbol is 24
multiply that control enter and then we
have that our basic template that i just
put some borders on there
ok I few things that i want to change
firstly with this one oh I want to
change the format of that first of all
blacks were number
ok some quick bite ok so if we change
the brakes a 223
we're going to get this long series of
numbers now we can come up here we can
format it to two decimal places
I'm reason I don't want to do that is
because when we give it to whoever else
will only be giving the two decimal
places so when they do that calculations
and when we do our calculations based on
this
it's actually going to be slightly at
all to one another
so all right around this sort of
watching in the formula itself so if i
do any calculations are not that it's
always going to be to two decimal places
on the round one the number one number
of digits want around it so it's going
to be two decimal places
press ctrl enter on that one and we now
have this round it off nicely and neatly
for any calculations we want to do and
give thing that I want to do is that
when we have this one going to clear
this by holding down the left mouse
button and just drag it through the
cells to get that great hash sign
through is the winner - now we're
actually gonna get all that so if I am
filling all these values
no I'm gonna get lots of not ugly
North's there which kind of unnecessary
so the typical way to do this is to use
an if statement and say if of that all
that all that all that if all of them
her all blank
then what we wanted to show a blanket
not sell otherwise to that formula that
so want to get if all there and we want
to say if or b2 is going to the range
and this is going to be to soco lon e 2
i'll be our range that if b to e 2
equals blank which double-quote
double-quote then what we want is a
blank there
don't want anything which such
double-quote double-quote otherwise I
know what I've done that if B - E - and
I want to end of my old function now
ok and be too if the All of Me 2 e 2
equals blank then we will now otherwise
of the value of false is you are not
round function that's control answer on
that one
and we get the value that's fine that's
because when you have b to e to like
that it's what's called an array formula
in excel so to get out to work running
pressing and you have to press ctrl
shift and enter and we'll do that that
ok so that will not work and then we
type in nine o'clock twelve o'clock
twelve we finally and clocking out and
17 o'clock now we got 27 half hours or
so . 35 which could simply 58 that
ok i also working nicely except for this
column now
ok so the problem that I have with this
one now
it's a typically when somebody takes a
25 minute break like that there actually
any entitled to heart for now so we need
to round that up too hard for now any
time that it falls below half an hour
so we'll put our hurdle in here of hard
for an hour and what we want to say is
ok let's take that whole if your mirror
again if or not going to be that be - to
eat
- if those are all blank
no clothes off the old formula of those
are blank finally with true when we want
to blank there and then if false
I want that formula that had before
except they will say if if hear this one
- the start if that 25 minutes
there is less than my heart heard all my
half an hour
ok those are less than half an hour than
what i want is I want it to show that
half now so be the time to clock that
- the time they clocked in - this half
an hour and then if ok notice if it's
true if it is less than fun if it's not
now if they taken 40 minutes or whatever
and I want to think that 40 minute break
don't pay them that 40 minutes anyway
how so
value if false is that if
that is what we had before that one -
that one plus the morning time which is
that one - that on know what we can do
is press ctrl shift and antics with
another array formula was from marrying
that ok
you know what I did wrong here
if you two wanna c 2 is less than that
then I want no other words on
okay and i just want to know that rocket
arrow ok that's good
control shift down - okay now i got my
second half hours now
when I feel that down there I get back
and now when I feel that down there
oh not goes like that ok now if I could
just stare 40 minute break there since
1240 she come down to the 720 and that's
a nice neat little 7.33 ok now if you
don't want to have this hurdle that
permanently all the time what you can do
is to is is to change this and in fact
your needs to whatever anyway because
there when we copy this down you know is
that I to their go to y 3 goes too high
for now you can do this one of two ways
either you can press f4 in there once
and that locks the formula
ok so that means that when you fill it
down now will always be pointing at that
control shift down to come back in there
for sure
enter ok so now when you copy this down
like that and you come to one of these
cells that that's still I to their that
is still I to there another way of doing
it what you take the value that we had
never have not she hard-coded into the
formula itself
so if you double click on it like I did
that come from the brown one now
ok double-click on the hurdle and then
what is press the f9 key and that will
come up with this ridiculous long series
of 30 recurring three's a charm and a
half an hour
ok so we do the same thing again press
f9 and now i want control shift enter on
there you have it
ok that's a slightly more complicated
way of doing a timesheet but if you use
this on a regular basis with stuff that
might be a few little things
your tricks they might want to just bear
in mind oh ok so that's it for me
thanks for watching