If you're using Microsoft Excel and you want to record your employees
time, I'm going to show you a pretty easy way to do it.
This sheet has been set up with something called Data Validation and
I'll show you how I did that. Highlight the column that you're going
to have the dates go into and go to the data ribbon. Left click on data
validation and set up a rule that you're only going to allow a date. Then
Excel also wants a range for the dates so do it between, and I just picked today
and then for the end date I picked the date pretty far out just so you don't
have to redo this. What this will do, whenever your user comes in, if they
don't put in a valid date, it's going to give them an error alert. We're going to
look at that, but before we do, look at the input message. I took this off because, if
you have an input message, it's going to be popping up every time you use the
sheet. But. after the first time or two, you're gonna know, so I don't think you
needed that. But, I did put in an error alert. I checked the box here. It's
going to show you this red X and it's going to tell you to enter a valid date if
the dates not right. Go ahead and click OK. I'm going to go to a cell, let's say
D8, and I'm gonna enter something that's not a valid date. Let's just say June
1st, 2018. Excel is not going to like this because of the "st". If you just said June
1, it would recognize it. If I hit enter, I'm going to get that warning sign. If
you do retry and you do 6, we can even use dashes, 6-1-18, that's going to be
valid. It'll recognize it. A similar thing was done with the time
column. If I highlight this and I go up to Data validation I said it has to
be a time and it has to be in between midnight and midnight. And there's going
to be, again, no input message because I don't think you want to see that
every single time. If it doesn't do a valid time, it's going to give you the
red X again and tell you enter a valid time. If I click OK here, what this is
going to prevent is 2 p.m. say without a space. Excel wants a space in between the
two and the p.m. won't let you. So let's retry. Let's do it as 14
military time, says no, but if you do 14:00 enter, that's okay. Or, if you do 2:00 pm,
that's okay as well. There's only so many ways to enter
valid dates and a valid time in Excel and if you don't get at least as far as that
point, no matter what times you use, it's not going to work. So, with these two data
validations, we've gotten to the point where we have valid inputs. And this timesheet
can also do overnight shifts. This person clocked in at 6/1/18 they're gonna
clock out at 6/2/18 and it's going to be at 3:02 in the morning a.m.. That's
a 13-hour shift. I'll show you the formula that we're using in column E.
I'll go into cell E9, double click, and what this is doing is this is adding
together row 9 to get June 2nd, 3:02 a.m. so that's C9 plus D9 and then it's
subtracting the value of June 1st 2:00 p.m. added together. The result of
these two being subtracted from each other is then multiplied by 24 because
it comes out as a fraction of a day so if you want to convert it from a
fraction of a day to hours. Multiply it by 24. There's your result. 13.03
hours. If the employee clocks in and out, you just add another
line. You can add as many lines to this as you want. There's no
limitation. I just made it small so that you could see it easily. The
bottom just uses the SUM formula to sum the column. Those are the hours right
there that you can pay your employees. I hope that was helpful and, if you liked
this tutorial, I have a lot of others just like it. You can subscribe in the
lower right-hand corner. Thanks!