Simple Time Sheet In Excel

hi there folks and welcome back to another tip for Microsoft Excel today we're going to be looking at a different option for you to build your own timesheet so a timesheet would be used to help the hours work or to keep track of maybe a contractor or a contract position or something informal you you wouldn't use this for like an entire organization well you could use a timesheet but we build a little bit differently this is going to be for managing the time of an individual so let's get started here the first thing we want to do is create a row that we can always see we don't want anything disappearing so to do that we're going to just freeze the cell make it stand out a little bit give it some information and so we're going to say date worked we're going to say time in and we're going to say time out not the kind of time out where you got to sit in the corner just the time when you get off work and you go and rest and so our next one is going to be total hours worked and and we're going to create one more and we're going to say gross pay you know it's not gross to ever get paid the gross pay is simply the total amount we're going to need a few pieces of data here and so we're going to just create a box over here that says hourly rate and we'll keep with the beautiful format here and we're going to make that one yellow also just put a nice little border on it and then we're going to format this in a dollar and we'll say that the hourly rate is thirty five dollars okay and let's say that just for kicks you want a additional information column so you know what it is the person is working on so we're going to open that up a little bit and we'll format it by wrapping the text so that they can put in as much information as they need and it won't get all crazy so now we got everything formatted and we're going to go ahead and put a border here real quick and then we're going to also format this and we'll just click on the entire column a right click go to format cells and we're going to come right over here to date and let's say we use this one here we want the day the month the date and the year and so let's say that we were creating a time log for May 4th 2017 and of course we want to format these too and so we highlight both of them and we're going to come over here to time we're just going to give it a basic 1:30 p.m. type format and so what that's going to do is let's say we clock in at 7 a.m. and we get off on this day at 5:00 p.m. well that's going to give us our time format in this entire column and this is formatted for date so this entire column is going to populate just like this in fact if we drag down we go to the fifth and let's say we drag down we go to the 6th ok so hopefully we're now working on Saturdays like this shows here but you'll see that the formatting makes it a lot cleaner and simpler to do so now to calculate the hours worked we need to add a formula in Excel so that it automatically populates based on the time in and time out and to do that what we do is we put in our equal sign and we're going to do an open bracket and we are going to always select the time out first and then we'll subtract that from the time in now we're going to close that and we'll hit enter and hope shows up 10:00 a.m. why does it do that well it's because we didn't format this so what we want to do is grab the entire column you see in the background of time is actually a number in Excel and we're about to see that here in just a second the later format is a rewrite click again go to format cells and we're just going to put a general formatting on there now we see that we get a point four one six seven which as you can tell is not the hours worked so why does Excel do that well Excel does that because when we work with x here this is calculating based on a 24 hour time period the way we correct that is we come up here and we simply multiply by 24 and we'll close that off and we see that we work ten hours on this day now what we want to do even more specifically is we want to copy this formula that we just built and then we want to come up here and select column D now we want to right-click and you'll see that there's a quite a few different paste options here so the one that we're actually going to look for is not the values we don't want that what we want is the formulas that are right here you also have an option for formatting for the links but we'll we what we want to paste in there is the formula in the entire column so we're going to select on that you see Excel is going to do its thing and now if we come over here on Friday May 5th 2017 let's say we're working really hard this day and we came in at 6:30 a.m. and we got oh at let's say 6:00 p.m. will now see that we put in 11 and a half hours of work on this day and Excel will automatically calculate that all the way down in this column so we obviously don't want this to have the formula in it the way we take care of that is we're just simply going to delete it out of this one box and we're going to say total hours worked and let's go ahead and keep this nice and pretty also and in the center so we get our total hours work next thing we need to do is calculate our gross pay this is how much money we're going to make for each individual day and a way that we'll do that is we're first going to format this and money and then we'll give this another unique shade just so that people know it's automated and they don't need to touch it and we're going to hit our equal sign okay and first things first we're going to select cell h4 and we're going to multiply that by hitting the star sign by the total hours worked which was d4 now we want to make sure that we lock this hourly rate so that when we copy it down it doesn't it doesn't change the number it's going to always stay with that 35 dollar or whatever is in the hourly rate box so we lock it again is by hitting f4 and then we're just going to hit enter and we'll see that on this day we made three hundred and fifty dollars so we're just going to copy that and we're going to select column e like we did with column D and we're going to right click and we're going to go ahead and paste in the formula you'll see Excel is doing its whole thing and it's going to take a little bit time because it's sending it down to every one of the cell and when we get done here when we get all done we're just going to change this top one and it'll say gross pay and so now we can see that as we put in our times let's say a p.m. because it's Saturday here here 8 a.m. excuse me - 10 a.m. we put in 2 hours we made 70 bucks there now we'd also like a column that tells us how much is due to us we want to know how much we've got to pay and so total pay sorry let's call that gross pay let's use official accounting terms here and we're going to highlight that yellow just give ourselves a little box there and now we've got our hourly rate which is $35 an hour and we've got our gross pay which is going to be the sum of all each day and so to do that we hit some Open bracket and we're just going to select this entire column then we're going to close it out and we see that we have eight hundred and twenty to fifty now let's say we come back in on well let's say that eight we worked pretty hard and so took a couple days off so it's Monday Monday the eights now and we're ready to go at it again we came in at 7 a.m. and we got off at 5 p.m. it's a normal normal day at work now you'll see that your gross pay over here automatically populates based on your total for the day and of course if you want any additional information you can just place it right in there each time you come in you just put the hours you worked in and it'll automatically populate the information you needed okay and that's the timesheet in Excel a pretty simple way to manage contracts or or one person's hours worked and the gross pay that you owe to them so hopefully the solution will help you today we learned about how to calculate the total hours worked and how to calculate the gross pay in Microsoft Excel subscribe to the station down below and we'll keep giving you tips for Microsoft Word PowerPoint and Excel and remember if you have any questions below or you're looking for a solution in any of those three applications for Microsoft go ahead and post your questions down below and we'll get back to you as soon as possible thanks for watching and have a wonderful day

Channel: EZ Learning

Views: 563,871

Rating: 4.7153845 out of 5

Keywords: time sheet in excel, excel time sheet, time sheet, building a time sheet in excel, excel, microsoft excel, excel tips and tricks, excel solutions

Id: HUjIzUXxud8

Length: 12min 6sec (726 seconds)

Published: Thu May 04 2017