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 time sheet to calculate the employee’s timings like IN and OUT timings, how many hours employee worked for a day, what is the exact “BREAK” time he has taken. In excel this time sheet will summarize a number of hours worked by each employee in order to calculate these timings we can use time sheet to elaborate it.
How to Create Timesheet Template in Excel?
To create 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. 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:
- Create a new column named as Number of Hours Worked.
- Make sure that the cell is in proper time format.
- In order to calculate employees 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 and 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 formula as shown above but in this example, we have BREAK TIME IN and TIME OUT Hence we can use the simple excel timesheet formula as =(E2-D2)+(G2-F2).
- So by applying this formula, we will come to 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 that how many hours an employee has worked and the breakup timings have been given for each employee as time OUT.
Excel Timesheet – Example #2
In the previous example, we have seen that how many hours an employee has been worked by using the normal arithmetic formula now in this example we will use the time function with 24 hours format.
Suppose assume that management wants to pay the employees on an hourly basis based on the timings they have worked.
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 get paid for Rs.20/- per hour and follow the below steps given below.
- Insert two new column 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 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 Number of Hours Worked. Let’s see with an example as follows.
Excel Timesheet – Example #3
Let’s consider the same employee data record which has TIME IN and TIME OUT record as follows.
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 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 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.
Lest see what happens if the Hourly Rate is changed to Rs.10/- and we will get the below result as follows.
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 time sheet for employees.
- Make sure that all cells are formatted in 24-hour format or else excel will throw a decimal value.
- Make sure that AM and PM are mentioned in the timings because in case if the OUT TIME is Greater than IN TIME excel will not able to calculate the no 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 downloadable excel template. You may also look at these following articles to learn more –
