Streamline Your Workflow with Our Timesheet Invoice Template for Quality Assurance

Timesheet invoice template for Quality Assurance

Today, we're going to automate  a bi-weekly timesheet in Excel   using special formulas to auto-populate the  dates and create a drop-down list selection   for the PTO type. We'll also see how  to calculate total hours, overtime,   and pay. Be sure to check out my other video  on how to create this template from scratch.   I also have two other videos on how to automate  a semi-monthly and a weekly timesheet. If you   want to get a jump start on your project, you  can purchase and download these templates. I'll   include a link in the description below this  video. I hope you like it. Let's take a look. So here's our bi-weekly timesheet template.  Notice up here in the pay period start date area,   if I enter a start date, I'm going to  enter a different date, say 7/21/24,   and hit enter. Notice that the pay  period end date automatically populates,   as well as all of the days and dates over here on  the timesheet. So again, if I change that date,   let's say our bi-weekly pay period starts  on a Monday instead of a Sunday. Let's   put 7/22/24 and hit enter. Notice that these  days and dates will automatically populate. The formulas that we use to create this notice  that what we can do is set up the pay period start   date as an entry cell for users to enter that  date. We have helper text set up to let users   know to enter that date, and then we have the pay  period end date automatically calculating. That's   because we have a formula up in that cell.  We say equals G3 (cell G3 that contains the   pay period start date) plus 13 because Excel  handles dates as numbers, so we can add the   number of days to include the current date. That  gives us a 14-day pay period. So we can put any   date in here as our pay period start date, and  then this date will calculate to be the 14th   day out from that date, capturing your 14-day  or bi-weekly pay period start and end dates. Down here in our table for the  timesheet, for the first date in the row,   we can select that cell and say equals G3 because  we want the first day of that pay period to be the   pay period start date. Those dates are going  to be equal. Then, the next formula down,   we're going to say C10 (this date that's in  the cell above) plus one will be the next day.   What we do is take our mouse to the right corner  where it turns into crosshairs, and this means we   can copy this formula down, automatically placing  that formula into each cell correctly. It will   add one to the cell above it. So notice C13  + 1, C14 + 1, and so forth, automatically   populating all the correct dates down here on  your timesheet for the bi-weekly pay period. To get the days to update correctly  based on the date in this column,   we use a different formula. I'm going to  click there. Notice the formula that we use,   and I'll include this in the description below  the video. It's equals TEXT(parentheses),   then you select the cell you want to reference  (the date in cell C10 in this case), and then we   say comma, space, quotes, dddd (for the day),  end quotes, and close those parentheses. Once   you have that formula in there for the first one,  you can also copy that all the way down the rows,   and it will copy that formula down. Notice  this cell is going to look at the date in   cell C11 and put the day of the week. That's  how we set that up to automatically populate. All of these dates and cells (days and dates)  are going to automatically populate just based   on entering the pay period start date up here.  Now, what we want to also do is make sure these   cells are formatted as a date; otherwise, they  might show up as a number. You can right-click,   click on Format Cells, and make sure Date is  selected, then click OK. Same thing over here:   you want all the cells in column C to be  formatted as a date, and you can format   the cells in column B as General because they're  going to show the day of the week as text. Over   here in the rest of your timesheet, you can select  everything where people will be inputting hours,   right-click, and format those cells as a  number to two decimal places, then click OK. To set up the drop-down list selection, we're  going to set up a drop-down list so users can   select the type of PTO they may want to enter on  their timesheet. To do that, we add an additional   tab to the workbook and create our drop-down list  selection there. In this case, I typed vacation,   sick, volunteer, jury, and leave as different  types of paid time off that someone could select   from. Over here, we can select the first cell  where we want to input that drop-down list. Go   to the Data tab, over to Data Tools, drop down  the Data Validation drop-down, and select Data   Validation. Under Allow, select List, and then in  the Source area, jump to the tab where you have   your list typed out and select all the drop-down  list items. You can go to your Input Message tab   if you want to have an input message (optional),  same with the Error Alert. Once you're done   programming that data validation, click OK. Now  you have a drop-down list where users can click   on that cell to enter a value, and they'll see a  little drop-down prompting them with the helper   text "Select PTO." They can select whatever type  of PTO they need to input. After setting up that   data validation in the first cell, hit Ctrl+C to  copy, select the rest of the rows in that column,   and hit Enter to paste. The data validation  table will show up in every cell there for you. Now we want to set up our calculations for total  hours. I'm going to select the cell for Total   Hours and notice that I have the SUM formula:  equals SUM, and then the range of cells from D   through F that will calculate the sum of those  total hours. Once I enter that, I can copy that   formula all the way down, and it will include  the sum of each row of hours on the timesheet.   You'll have a column for total hours for each day  that you can reference. Down here at the bottom   section, we have an area to calculate the rest  of the information needed for our timesheet. This   will calculate total hours. This is a timesheet  where employees are entering whole hours, not time   in and time out, so it will add up all the regular  hours worked and recorded during this pay period.   To do that, set up a SUM formula and select all  the cells in that column to sum together. Drag   that formula over to the other two cells, and it  will set up and sum both the overtime and any PTO   or time off hours recorded on the timesheet. Over  here, simply sum all the total hours in the cell. The next input cell for your users (or anyone  setting up this spreadsheet for use) will be   the rate per hour (somebody's base rate of pay).  Once that's entered, we have a cell that will   automatically calculate the overtime rate of pay,  which is the base rate of pay times 1.5. If that   is different at your organization for any reason,  update the formula there: simply say equals this   cell (your base rate of pay) times whatever factor  you pay for your overtime rate. If the paid time   off is paid at the same rate as the base pay rate,  simply say equals D25 (the base rate of pay). Below, we can calculate total  pay. In these formulas, we're   multiplying the total hours by the rate  per hour, giving us a total. Over here,   multiply the overtime hours by the overtime  rate, giving us a total. Then, multiply the   total paid time off hours by their base rate  of pay, giving us a total. In the totals,   simply sum all these totals below: say equals  SUM, then select the range of D26 to F26. Once you're done setting up your timesheet, you  can hide the PTO Type tab where you set up your   drop-down list selection. Click on that tab,  right-click, and hide it. You could duplicate   the bi-weekly tab 26 times and create an entire  workbook with all your pay period start dates   set out for the whole year: right-click on  your tab, say Move or Copy, move to the end,   create a copy, and click OK. Update the date  for the next week's pay period and save it. Be sure to check out my other videos. I'll show  you how to automate a semi-monthly timesheet with   some neat tricks using conditional formatting to  hide any days from the timesheet that don't fit in   the pay period. I'll also cover a weekly timesheet  where we'll automate the time in and time out,   accounting for any breaks and calculating  for overtime for hours worked over 40 in a   work week. Be sure to subscribe to my channel  for more videos like this. If you liked it,   share this video with all your friends  and colleagues. You can visit my website,   SharonSmithHR.com. Thank you so much for  watching, and I'll see you next time.