How to Create a TimeSheet in Excel – Tutorial
Excel is one of the most popular tools used in small business used for keeping record of routine and periodic business activities. Among numerous other functions, Excel provides easy-to-use timesheet templates function to track of timings of employees working hours.
In this article we will set up a system for employees’ tracking time. But before we proceed, there are a few things we should consider. There is no single sheet that can accommodate workhours for all the employees for the reason that this factor is unique to every employee according to his shift and position. However, there are certain features and basic datathat are common to most timesheet models. Planning of timesheet starts with collection this basic information, which includes the reason of tracking time, for whom will this sample timesheet be used, and how will this time-tracking be used.
For time tracking, we obviously require time values but we cannot work with these values. MS Excel spreadsheet formulae make it easy to convert these values into workable integers which can be used easily in various calculations, workhours in this case. Generally, an “IF” function formula can convert values and differentiate between regular and overtime hours. With this basic calculation of regular and over time, we can then easily calculate employee’s pay or regular and over time working hours. Now, let’s get started on preparing timesheet on Excel.
> The Column Headers
First of all we will fill in headers. Headers will show what is our basic need, i.e. managing working hours of the employees. For this, type “Time In” in cell B1, “Time Out” in cell C1, “Time In” again in cell D1, and “Time Out” again in cell E1. Type “Regular” and “OT” in cellsF1 and G1 respectively. (OT = Over Time)
> Week Days
Next we will mark the day of a week for which working hours are to be calculated. Type “Mon” (for Monday) in cell A2. Click the Fill Handle in the lower-right corner of cell A2 and drag it down to A8. All the week days will now under “Mon” appear as shown below.
> The Text Labels forthe Weekly Totals
Now we will enter the core purpose of our timesheet, payment made to be made to the employees. For this type “Total Hours”, “Hourly Rate”, and “Total Pay” in cellsE9, E10and E11 respectively.
> The Formula for Calculating Regular Hours
We can automate our work by adding some formulae for calculating the Working hours. Enter in cell F2the formula =IF((((C2-B2)+(E2-D2))*24)>8,8,((C2-B2)+(E2-D2))*24). This formula calculates working time from cell B through E and converts the time value to integers.
> The Formula for Calculating Overtime Hours
Repeat the same procedure for calculating Overtime Hours. Use this formula in cell G2=IF(((C2-B2)+(E2-D2))*24>8,((C2-B2)+(E2-D2))*24-8,0). This will calculate working hours from column B through Column E and convert these values to integers.
> The Formulas for Working Hours and Pay
- Enter =F10*F9for regular pay in cell F11
- Enter =G9*G10for the amount of overtime pay in cell G11
- Enter =SUM(F2:F8) for the number of regular hours worked in cell F9
- Enter =SUM(G2:G8)for the number of overtime hours worked in cell G9
- Enter =F10*1.5for the overtime pay rate in cell G10
- Enter =SUM(F11:G11)for the total gross pay in cell H11
PS: All the necessary formulaeare now entered into the Excel timesheet.
> Format the Numerical Values
It is important to choose and mark your units to be used in the timesheet. Select all the cells spared for entering timings; in this case B2 through E8.Right-click and select Format cells from the menu and click the Numbers tab in the pop-up window. From the Category menu on the left,select Time;and select “1:30PM” from the Type menu pic your style of showing time. Click OK.
Next, select cells marked for pays; in this case F11, G11 and H11. Again, open the formatting window and select symbol $ present in Currency category in the Number tab.
Note:Alternatively, these formatting steps can also be carried through a shorter step. Select the cells that need to be formatted. Then change the formatting from Numbers group in the Home tab in the ribbon. Here you can change the Type, Currency and decimal places.
> Adding Shades and Borders
Now we will give some colors to our timesheet. This will make it more professional and likeable to be used frequently. This is also a way to focus on the more important items or cells in the worksheet.
- Select all the cells and areas in the timesheet that will not be used. These include the column headers, the column for weekdays,column H up to cell H10 and all the surrounding cells that will remain empty or unused. When all the cells are selected, use the Fill Color button in Font group under Home tab to fill a color. If you selected darker shade then change the color of text in the selected text.
- Next select cells that will be used for Data Entry i.e. A2 through G8 and F9 through G11. Apply borders by clicking “All Borders” from the drop-down menu in Font group under Home tab.
> Testing For Accuracy
- Type “9:00 AM” in cell B2, “12:00 PM” in cell C2 and “1:00 PM” in cell D2. Select cells B2 through D2 and copy the values down through to row 6 (Friday row). This will mark your regular time in for work (9:00 AM), break for lunch (12:00) and return from lunch (1:00)
- We will enter the time out from work separately and independently as this is factor that determines regular or overtime working hour. Type “5:00 PM”, “7:00 PM”, “6:00PM”, “6:00PM” and “8:00PM” in cells E1 through E6.
- Enter the pay rate $12 in cell F10.
- The payments should read as such:
- Regular Pay = $468
- Overtime Pay = $54
- Total Pay = $522
- If the values returned do not match the example, check the formulae again. The Excel timesheet spreadsheet Microsoft Template is complete and tested.
> Save the WorkBook
After the timesheet is completed and test for correct formulae, the cell entries can be cleared. Keep everything else in the timesheet; including headers, weekdays and most importantly the formulae. Save the file with a name that is easy to remember and recognize.