Get the free calculating your paycheck salary worksheet 1 form

Video instructions and help with filling out and completing calculating your paycheck salary worksheet 1

Instructions and Help about calculating overtime pay worksheet answers form

Welcome to another free tip of the week brought to you by Excel learning zone comm I am Richard Ross in this lesson we're going to talk about calculating overtime pay using Microsoft Excel in this lesson we're going to take a simple employee timesheet that's got the employee name the pay rate the number of hours they've worked for the week, and we're going to say okay if they've worked up to 40 hours calculate normal pay if they've worked up to 50 hours calculate time and a half for those extra 10 hours if they work them and for double-time it's anything over 50 hours how do we do that in Excel well let's take a look this week's tip is about calculating employee over time here I have a real simple list of employees each employee pay rate and the hours they worked Monday through Saturday each day the first thing I'll do is calculate the total hours that that employee worked for the week, so this will be total hours, and we'll say equals the sum open parenthesis and then select our range right there press ENTER and this employee worked 41 hours that week I'll click and autofill down and there are the totals for each employee notice I'm getting a little error message drop that box down it says formula omits adjacent cells that just means that Excel sees more data next to the range the pay rate over here for example and thinks that maybe that should be included I'll hit ignore because in this case Excel is wrong, so now I have the total hours next I want to say if the employee has worked 40 hours or less they get paid standard time just one times their pay rate if they've worked between 40 and 50 hours they get paid time and a half and 50 hours or more they get paid double time for those extra hours so first let's calculate how many normal hours they've worked normal hours is anything 40 and under we can calculate that with a simple if function equals is I to is where their total hours are located if I 2 is less than or equal to 4 D comma put that number here I to otherwise put a 40 here if they worked more than 40 hours we're going to credit them with 40 and if I ought to fill this down you can see if they work more than 40 hours such as these guys they get a 40 otherwise they get the number of hours they worked that's the total number of normal hours that each employee has worked now to figure out time-and-a-half we're going to actually use two columns here the first column we're going to figure out how many hours over that they've worked in the week all right so another if function equals is if I 2 is greater than 40 then put in here I 2 minus 40 so the difference between how many they actually worked and 40 otherwise 0 because they haven't worked any time and a half hours and will autofill that down, and you can see here this is actually the difference between the total hours they worked and 40 so this guy's one for 41 11 for 5135 because this guy worked 75 and these are both 0 because this person didn't work more than 40 hours, so we'll call...