Timesheet in Excel Template (Table of Contents)
Timesheet in Excel
Timesheet is a system for recording the number of employee’s time spent on each job. In excel, we normally use a timesheet to calculate the employee’s timings like IN and OUT timings, how many hours an employee worked for a day, what is the exact “BREAK” time he has taken. In excel, this timesheet will summarize the number of hours worked by each employee; in order to calculate these timings, we can use the timesheet to elaborate it.
How to Create Timesheet Template in Excel?
Creating a timesheet template in excel is very simple and easy. Let’s understand how to create a timesheet template in excel with some examples.
Excel Timesheet – Example #1
In this example, we are going to create a timesheet of employees by calculating how much hours an employee worked. First, let’s consider the below employee database with IN and OUT Timings.
The above employee database has a date, employee name, TIME IN, and TIME OUT. Now we need to find out how much hours an employee worked by following the below steps:
- First, create a new column named Number of Hours Worked.
- Make sure that the cell is in proper time format.
- In order to calculate an employee’s no of hours, we will calculate the formula as OUT TIMINGS – IN TIMINGS.
- By default, Excel will return the result in decimal numbers, as shown below, where it’s not an exact number of hours.
- We need to change this general format to time by formatting the cell, as shown below.
- Once we click on the format cell, we will get the below dialog box, choose the exact time format, and then click on ok.
- After applying the format, we will get the output result as follows.
- If we have IN and OUT Timings, we can use the formula as shown above, but we have BREAK TIME IN and TIME OUT in this example. Hence we can use the simple excel timesheet formula as =(E2-D2)+(G2-F2).
- By applying this formula, we will know how many hours an employee worked for a day, and the output will be displayed as follows.
- We just need to drag the cell H2 downwards, and the formula will be applied for all the cells.
In the above example, we can see the time difference in how many hours an employee has worked and the breakup timings given for each employee as time OUT.
Excel Timesheet – Example #2
In the previous example, we have seen how many hours an employee has been worked using the normal arithmetic formula; now, in this example, we will use the time function in 24 hours format.
4.9 (8,735 ratings)
View Course
Suppose assume that management wants to pay the employees on an hourly basis based on their work timings.
Let’s consider the employee database with appropriate timings record, as shown below.
Here we can see that employee IN and OUT timings for various employees. Now we need to calculate the number of hours employee has been worked by following with the below steps:
- Insert new column named Hours Worked.
- Apply the normal excel timesheet formula as =E2-D2, as shown below.
- Now we can see that decimal values have been appeared for Hours Worked.
- This error normally occurs because the time is not in 24-hour format.
- To apply the excel timesheet formula by multiplying by 24 as =(E2-D2)*24.
- After applying the above formula, we will get the output result as follows.
- Drag the cell F2 downwards, and the formula will be applied for all the cells, as shown below.
Now we will calculate how many employees are going to get paid on an hourly basis. Let’s assume that an employee will be paid for Rs.20/- per hour and follow the below steps.
- Insert two new columns as Hourly Rate and Gross Pay as given below.
- In hourly Rate Column enter Rs.20/-.
- To apply the excel timesheet formula as Gross Pay = Hourly Rate * Hours Worked.
- We will get the below result as follows.
- Drag the cell H2 downwards, and the formula will be applied for all the cells, as shown below.
Here we calculated the Gross Pay of an employee based on the Number of Hours Worked.
Either way, we can create a new column Hourly Rate as Rs.20/- in a fixed column and multiply it by the Number of Hours Worked. Let’s see with an example as follows.
Excel Timesheet – Example #3
Consider the same employee data record, which has TIME IN and TIME OUT records.
Here we have created a new fixed column named Hourly Rate as Rs.30/-. So employee will be get paid Rs.30/- Per Hour.
Now Hourly Rate has a fixed column, so whenever the Rate changes, it will get populated and reflected in the Gross Pay column as shown in the below steps.
- Insert the excel timesheet formula in Gross Pay Column as =$c$1*F5 shown below, i.e. Gross Pay = Hourly Rate * Hours Worked.
- We can see that Gross Pay has been calculated as per the Hourly Rate Basis.
- Drag the cell G2 downwards, and the formula will be applied for all the cells, as shown below.
- Next, we will calculate the Total Gross Pay by adding the Gross Pay of the employees.
- Use the formula =SUM(G5:G19).
- We will get the below output as follows.
Hence we have calculated Total Gross Pay as Rs.3900/- and Hourly Rate as Rs.30/- If the Hours Rate changes, the values will be get automatically changed, and the same Gross Total also will be get changed.
Let’s see what happens if the Hourly Rate is changed to Rs.10/- and we will get the below result.
Now we can see the difference in each employee that Gross Pay has been getting decreased because of Changes in Hourly Rate and at the same time we got Total Gross Pay as Rs.1300/- and Hourly Rate as Rs.10/.
Things to Remember
- Maintain proper timing format while creating a timesheet for employees.
- Make sure that all cells are formatted in 24-hour format, or else excel will throw a decimal value.
- Ensure that AM and PM are mentioned in the timings because if the OUT TIME is Greater than IN TIME, excel will not be able to calculate the number of hours worked.
Recommended Articles
This has been a guide to Timesheet in Excel. Here we discuss how to create a Timesheet Template in Excel along with practical examples and a downloadable excel template. You may also look at the following articles to learn more –
