Byline Weekly Timesheet Made Easy

E signature weekly timesheet template

hey guys it's Randall with digital designs I'm going to show you today how to make a timesheet in Microsoft Excel this timesheet will calculate the hours an employee has worked based on the time they clock in and the time they clock out it will remove lunches and give you total hours total pay plus overtime hours and overtime pay this is all calculated by Microsoft Excel so I'm going to start by creating the initial Excel document this is just going to be text so I am going to speed through this and then we will get to the calculations all right so we have our Excel document set up here we're going to start by entering our hourly wage which is 1525 overtime rate which is time and a half and we're going to ask a cell to figure out our hourly wage or overtime and this argument would be the following all right and I'm going to explain what it's doing it's telling McCollum filling Excel diet column B 3 or rather multiply B 3 by B 4 and that gives us our overtime wage of 2288 an hour now we're going to go here and do figure out our hours for each day using Auto sum and I'll tell you beforehand this is going to add the times wrong and I did that on purpose because I want to show you a common error that people make with Excel when using Auto sum first let me explain what this calculation is doing telling Excel to take column C 9 and minus it from b9 which will take clock-in and subtract it from lunch start add that to lunch and subtracted from clock out giving us 2 the law or saying if you click enter we get a calculation but it's obviously wrong we have not worked 21 hours there's a simple reason for this if you format cells you find that it's in the time format so it's I try to figure out the time to addition and subtraction so instead we're going to go to a number these we're trying to figure out the number and there we go regular hours so this field these fields should all be number decimal places - you get 988 so that gives us our regular hours and if you go here do format cells this should be you can use any time format you like I like to go to custom use ah am/pm H hour minute minute and p.m. we're going to do that for the rest of these if you are doing Auto song calculations and it isn't adding up it's probably because you either have done the equation wrong or you forgot to format the cell correctly those are common reasons why people get frustrated with Excel we're going to continue here and finish doing these equations and efforts looking at this it may seem like a lot to remember but it's really simple if you look at what it's actually calculating it's calculating column columns which number column and doing math simple laughs and I did forget to close the equation you okay so we have our total hours now we can go ahead and do the equation for Saturday and Sunday on obviously we're not working Saturday and Sunday in this template so we're going to go ahead and skip that for now I'm going to go down here and figure out our regular hours we're going to create an equation equals some column f9f 15 what that's doing is it's adding all of our hours up from what we just calculated and I actually did that wrong I'm going to copy that and delete it that should go under total that's the total hours worked a regular hours is going to be 40 so we're going to create an argument here we're going to go minimum 40 and there we go 40 hours so then we're going to need to subtract that from our total hours figured out max zero the 21 minus 40 there we go so taken total hours - regular hours which is 40 is we did minimum 40 there's our overtime hours now we need to calculate pay going to do equal b19 going to take our regular hours multiplied by column B 3 which is our hourly wage and we're going to do the same thing with this field equals B 20 it's going to take our overtime hours column B 20 multiplied by column B 5 which is our overtime wage and that's $190 overtime pay now we need to calculate the total we're going to do a sequel sum and all that does is take both fields and add them so $800 total I'm go ahead and change this to currency it looks nicer right there you have a simple template for creating a time sheet for employees calculating hours based on clock in clock out and eliminating lunches and it's really nice because you can change anything in this template for instance if the employees supposed to work say 30 hours before our time which I'm not so that is the case in any state but if it was that would change one need have to change both arguments here so you can change these equations very easily what just clicked I hope you enjoyed this tutorial hopefully it was helpful to you and please feel free to comment below send suggestions questions comments we'd love to hear from you and thank you for watching