Employers use time sheets to track their employees’ hours to ensure they are being productive and are arriving or leaving work on time. Some employers may use a time clock to monitor their hourly employees’ time, others may use time sheets created on a spreadsheet. An employer can create a basic time sheet reflecting the employee’s time in and time out, or he can format it to include the employee’s lunch breaks and overtime hours. Preparing a time sheet using Microsoft Excel is a specific but relatively easy process.
Basic Time Sheet in Excel
Make a basic time sheet by creating five columns (A, B, C and D). Label each heading Date, Log In, Log Out and Total Hours respectively. Enter the days of the week under column A. Type the work start time in Column B (for example, 9 a.m.). Be consistent in the method used to enter the time such as military time or regular time. Enter the work end time under Column C.
Go to the Total Hours column (D) and enter the following formula to calculate the work hours: =SUM(C2-B2)*24. Right-click and go to “Format” to change the cell format to Number at two decimal places.
Under Cell D9, use formula =SUM(D2:D8) to calculate the total hours worked.
Lunch Time Sheet in Excel
Create a time sheet with unpaid lunch breaks. Use the instructions in Step 1 to create a basic time sheet. Insert two columns between Log In and Log Out; title as Lunch In, Lunch Out. To calculate work hours, under cell F2, enter formula =SUM((E2-B2)-(D2-C2))*24.
Overtime Time Sheet in Excel
Make an Excel time sheet with overtime hours. Use the instructions above to create a basic time sheet or to create one that includes unpaid lunch breaks. Change the Total Hours column to Regular Hours and the Total Pay column to Overtime Hours. Go to “Format” and format the Regular and Overtime columns (F and G) to two decimal places.
To calculate the regular hours worked, under cell F2, type =IF((((C2-B2)+(E2-D2))*24)>8,8,((C2-B2)+(E2-D2))*24). To calculate overtime hours worked, under cell G2 enter =IF(((C2-B2)+(E2-D2))*24>8, ((C2-B2)+(E2-D2))*24-8,0). Drag the formula down through the remaining days of the week.
To total regular hours, under cell F10, type =SUM(F2:F8). To total overtime hours, under cell G10, enter =SUM(G2:G8).
The employer can format his time sheet to suit his needs. For instance, if he requires a column reflecting the employee's pay, he can include that column with the formula for calculating the pay.
Grace Ferguson - Grace Ferguson has been writing since 2000. She writes website content and other areas of nonfiction regularly for private clients. Grace ...
Join the Conversation