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.
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)
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.
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.
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.
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.
PS: All the necessary formulaeare now entered into the Excel timesheet.
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.
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.
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.