Effortlessly Create Your Timesheet Invoice Template for Planning

Timesheet invoice template for Planning

I'm going to show you how to automate a weekly  timesheet in Excel using special formulas to   auto-populate the dates and the pay period  range. Then, I'll show you how to calculate   the time in and time out, including breaks,  and how to properly calculate overtime for   hours worked over 40 in a workweek. Be sure  to check out my other videos on timesheets;   you'll see how to automate a semi-monthly  and a bi-weekly timesheet. If you want to   get a jump start, you can purchase these  templates and download them. I'll include   a link in the description below, or you  can visit my website, SharonSmithHR.com. I hope you like this video. Here,  we have our weekly timesheet,   and we have a cell where we're going  to enter the pay period start date,   and we have the pay period end date that will  automatically calculate. It's going to give us a   weekly spread of time because we're going to take  that pay period start date, which is in cell G3,   and add six days to that. We're going to make sure  that all of our date cells are formatted as dates,   so we'll right-click, click on "Format Cells,"  and make sure that it's formatted as a date so   it won't appear as a number. Then, we're going  to come down to our timesheet. I'm going to   scroll down a little bit, and we can see all of  the days in the week on our weekly timesheet. The first date is going to equal cell G3,  which is the pay period start date. Then,   the next cell underneath that is going  to take the cell above it (C10 + 1),   and then we drag that formula down so that we have  all of the dates that will automatically populate   based on just entering the pay period start date.  Then, we also have the day of the week that will   automatically populate based on the date that  it's referencing in the cell next to it. The   formula that we use to populate the day of the  week is =TEXT( and then we select the cell of   the date that we want to reference, comma, space,  quotes, "dddd," end quotes, and close parenthesis.   That gives us the day of the week, and then we  can copy that formula all the way down as well. Now, notice what happens if I change the pay  period start date. Let's say our workweek   starts on a Monday instead of a Sunday. We  can put 6/24/24 and hit enter, and that's   going to update all of the dates and all of  the days of the week on our weekly timesheet. The next thing we want to do is format for our  data entry of the time in and time out so users   can input their time on their timesheet. We  want to select all of the cells for each row   of the week from cells D all the way through G.  What we want to do is right-click and click on   "Format Cells" and make sure that we have a  custom format. We're going to click "Custom"   and then scroll down to the format where they  can enter the time as the hour colon minutes  space and then capital AM or capital PM,  and then click OK. When we set that up,   we want to make sure to give users special  instructions to make sure that they type   their time in that exact format. That way,  we can calculate everything correctly. Once we have that set up, if a user comes down,  let's say they can input "8:45 AM," enter,   and it will automatically format the time to that  specification. Then, we want to take a look at our   column for the total hours worked each day. We  want to auto-calculate the total hours worked,   and we do this by taking the time out  at the end of the day minus the time in,   giving us the whole amount of time that  you work for the whole day. But then,   we want to subtract the time that you might have  taken for a break in the middle of the day. Then,   we want to multiply that by 24 because there  are 24 hours in a day, and that'll give us   the total hours worked in a full day. We can  copy that formula all the way down, and then   down here under "Total Hours," we can sum all of  the total hours worked for our weekly timesheet. Once we have the cell with the total hours,  then we can calculate our regular hours and   our overtime hours. The formula we use to  do that ensures that we can have up to 40   hours but not more than 40 hours in this cell  (=MIN(H17,40)). Likewise, for the overtime, we   don't want to have a negative number in there, so  if somebody doesn't work 40 hours in the workweek,   we don't want to have a negative number show up.  So, that will show up as zero. We use the formula   =MAX(0,H17-40) and we reference the cell with the  total hours minus 40, and close parentheses. Those   are the formulas we use to control that and  make sure that we have accurate timekeeping. Then, the pay rate can be entered here,  so if someone's pay rate changes or you   need to update that, then we have the  overtime rate of pay that automatically   calculates based on the base pay rate  times 1.5 (so that's time and a half)   for any hours worked over 40 in a workweek. If  that factor is different at your organization,   make sure you update that formula there. Then,  we have our totals for regular pay and overtime   pay. We're going to take our base rate of pay  times the regular hours, and then for overtime,   we'll take the overtime rate of pay times the  overtime hours, and that's going to give us a   total pay on our weekly timesheet when we sum  those regular and overtime hours together. Now, if we want to make sure that our timesheet  prints to one page, or if we save it as a PDF,   that it displays on one page, all we have  to do is come up to our "Page Layout" tab,   go to "Margins," click on "Custom Margins,"  and then on the "Page" tab, make sure that   this button is checked that says "Fit to 1  page wide by 1 page tall," and click OK. Then,   if you want to create extra tabs on your  workbook for other weeks that you want to track,   all you have to do is go down to your tab,  right-click, say "Move or Copy," move to end,   create a copy, and click OK. This duplicates that  weekly timesheet. You can come up, enter a new   pay period start date, and start fresh, and have a  new timesheet for each week. If it's for the whole   year, you can create this for 52 weeks out of the  year, or we can set up a macro that will create   all of those tabs for us automatically. It's  really easy, so I'll show you how to do that. All we have to do is copy our weekly timesheet  by selecting all and hit Ctrl+C to copy. Then,   come up and open up a new worksheet, and then  we're going to hit Ctrl+V to paste. Let's go   up to "View" and turn off our gridlines. All  right, now let's format this first tab for our   Week 1. We're going to come down and name the  tab "Week 1," and we're going to put the pay   period start date as the first week of the  year. Notice that starts on a Monday. Then,   we're going to put the name of the employee  that we want to create this workbook for. Now we can adjust our formatting, and I'm going  to clear out all of the time that's entered in,   so it'll be a blank timesheet. We're just going  to select all those cells and hit delete, and   then make sure that that person's base pay rate is  entered here in the cell. Now, what we want to do   is create a macro that's going to automatically  create all the other tabs for the rest of the 52   weeks in the year. That way, we can provide this  workbook to the employee to fill in for each week   for their time. What I'm going to do now is come  up to the "Developer" tab and click on "Visual   Basic." We're going to make sure that we're in  the current workbook, and we're going to click   on "Insert Module." Now, I have some VBA code that  I'm going to paste over here. I'll include it in   the description below, and I'll explain what this  is doing. I'm just going to copy and paste the   code here. In this VBA code, what it is, is we're  going to look at this current workbook, we're   going to look at the Week 1 tab as an example,  and what it's going to do is it's going to loop to   create tabs for weeks 2 through 52, and then it's  going to rename each of those tabs for each week,   and then it's also going to update that pay  period start date on each of those tabs. All right, now all we have to do is click  save, and it's going to make sure that we   save our workbook. We can call this the  person's name and "Weekly Timesheets,"   and now the file type, we want to drop down  and save this as a macro-enabled workbook,   and click save. Now we can resave our VBA code  and close out. To run the macro, all we have to   do is come up to our macros, select the "Create  Weekly Timesheets" macro, and click on "Run." When it's finished, it says that 52 weekly  timesheets were created successfully,   and we can click OK. Notice that Week 52 down  here at the bottom has the pay period start   date as 12/23, and so all of these dates  are automatically updated. If we look back,   say, at Week 47 and Week 44, notice  that those pay period start dates   are automatically set up for you. Now you  can provide this employee with an entire   year's worth of weekly timesheets that  they can fill in on each tab and save. Be sure to share this video with  your friends and check out my other   videos on timesheet templates. You can  visit my website, SharonSmithHR.com,   and subscribe to my channel. Thanks for  watching, and I'll see you next time.