How To Create A Timesheet Formula In Excel

Timesheet in Excel

Timesheet in Excel Template (Table of Contents)

Timesheet in Excel
How to Create Timesheet Template in Excel?

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.

You can download this Timesheet Excel Template here – Timesheet Excel Template

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.

Example 1-1

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.

Timesheet in Excel Example 1-2

By default, Excel will return the result in decimal numbers, as shown below, where it's not an exact number of hours.

Timesheet in Excel Example 1-3

We need to change this general format to time by formatting the cell, as shown below.

Timesheet in Excel Example 1-4

Once we click on the format cell, we will get the below dialog box, choose the exact time format, and then click on ok.

Timesheet in Excel Example 1-5

After applying the format, we will get the output result as follows.

Timesheet in Excel Example 1-6

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).

Timesheet in Excel Example 1-7

By applying this formula, we will know how many hours an employee worked for a day, and the output will be displayed as follows.

Timesheet in Excel Example 1-8

We just need to drag the cell H2 downwards, and the formula will be applied for all the cells.

Result of Example 1

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.

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.

Example 2-1

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.

Timesheet in Excel Example 2-2

Now we can see that decimal values have been appeared for Hours Worked.

Timesheet in Excel Example 2-3

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.

Timesheet in Excel Example 2-4

After applying the above formula, we will get the output result as follows.

Timesheet in Excel Example 2-5

Drag the cell F2 downwards, and the formula will be applied for all the cells, as shown below.

Timesheet in Excel Example 2-6

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.

Timesheet in Excel Example 2-6

In hourly Rate Column enter Rs.20/-.

Timesheet in Excel Example 2-7

To apply the excel timesheet formula asGross Pay = Hourly Rate * Hours Worked.

Timesheet in Excel Example 2-8

We will get the below result as follows.

Timesheet in Excel Example 2-9

Drag the cell H2 downwards, and the formula will be applied for all the cells, as shown below.

Result of Example 2

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.

Example 3-1

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.

Calculation of Gross Pay 1

We can see that Gross Pay has been calculated as per the Hourly Rate Basis.

Result of Gross Pay 1

Drag the cell G2 downwards, and the formula will be applied for all the cells, as shown below.

Drag the Formula

Next, we will calculate the Total Gross Pay by adding the Gross Pay of the employees.
Use the formula =SUM(G5:G19).

Timesheet in Excel Example 3-5

We will get the below output as follows.

Timesheet in Excel Example 3-6

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.

Result of Example 3

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.