Timesheet Calculator In Excel

Updated on March 8, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Excel Timesheet Calculator?

Excel Timesheet Calculator, as the name suggests, the timesheet is a method used in recording time. In the timesheet method, we enter a person’s time in and time out to calculate the total time spent on the job. Originally, it was developed for an employer to calculate payroll considering the lunch or the break time taken by the person.

Key Takeaways

  1. Timesheet in Excel is a timekeeping system. It tracks a person’s time devoted to the job. Also, it enables employers to evaluate payroll, taking lunch or break time into account by inserting their time in and out. 
  2. One may also use an Excel Format Painter brush to make a copy of a similar format for the cells. 
  3. The timesheet calculator in Excel is fundamentally dependent on formatting. 
  4. To evaluate the time correctly, one must utilize the time and minutes pattern, which is called h: mm.

How To Create Timesheet Calculator In Excel?

The timesheet uses the following inputs from the user:

You can download this Time Sheet Excel Template here – Time Sheet Excel Template
  1. In time
  2. Out Time
  3. Break-In Time
  4. Break Out time

The user can manually enter these inputs to calculate the total time spent.

We subtract the “In Time” from “Break in Time” and “Out Time” from “Break out Time” to exclude the break time taken by the employee.

You can download this Time Sheet Excel Template here – Time Sheet Excel Template

Examples

Example #1

The timesheet calculator in Excel records the time a person worked on a job. In the first example, we will learn about the basic timesheet and move to a professional one.

The format of the Excel timesheet looks like this:

Time sheet in Excel example 1

The user will manually enter the values in date, time in, time out, lunch in, and lunch out. And apply the Excel timesheet calculation with formulas in which it will calculate total working hours.

Below are the steps for creating a timesheet calculator in Excel:

  1. First, in cell F1, write an Excel equation.


    Time sheet in Excel example 1-1

    The total working hours are calculated by the total time spent by a person while deducting the lunchtime taken by the person.

  2. Drag the formula to cell F6 as we will make 5 entries.


    Time sheet in Excel example 1-2

  3. Now, input the date and the rest of the values.


    Time sheet in Excel example 1-3

    Our first timesheet calculates the total hours a person spends on a job.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Example #2

Let us professionally make a timesheet.

For a company to calculate the time worked by its employees and the overtime time that has been done, it needs to calculate the payroll.

The standard working hours for the company are 8 hours. For the mandatory 8 hours, the pay is ₹500, while for overtime, the pay is ₹650.

The format is as below:

TimeSheet in Excel example 2
  • Step 1 – First, let us input the standard working hours in cell K2.
TimeSheet in Excel example 1

We have used the TIME Excel function so that the hours are 8 hours, 0 minutes, and 0 seconds. Anything above that will be considered overtime.

  • Step 2 – In Excel, getting the format correct is the key. Let us insert the formula in the F2 cell to calculate the total time worked by an employee.
TimeSheet in Excel example 2
  • Step 3 – We want our cell to be in the correct format, so right-click on the cell and go to the “Format Cells” and select the “General” category from the “Number” section.
TimeSheet in Excel example 3
  • Step 4 – Go to “Custom” and select “h:mm” for hours and minutes.
TimeSheet in Excel example 4
  • Step 5 – Drag it to cell F6.
TimeSheet in Excel example 5
  • Step 6 – In the “Regular” column, the regular hours are eight hours a day but let us say if the total number of working hours exceeds eight hours a day. Then, it should display the total working hours, if not the regular eight hours.

For this, we can use an IF function. In cell G2, write the Excel timesheet formula:

TimeSheet in Excel example 6

We have locked cell K1 as it will not change as a reference.

  • Step 7 – Drag cell G2 to G6.
TimeSheet in Excel example 7
  • Step 8 – Cell G2 is showing an incorrect value because of incorrect formatting.

So now, to get the correct value, we need to click on cell G2. Press right-click, then go to the “Format Cells” section, and in “Custom,” select “h:mm” and click “OK.”

TimeSheet in Excel example 8
example 9
  • Step 10 – Now, let us calculate overtime hours worked by an employee. In cell H2, write the Excel timesheet formula:
example 10

It will calculate the overtime done by an employee.

  • Step 11 – Drag this to cell H6.
example 11
  • Step 12 – Always remember to check the correct format. Right-click on the selected cells, and go to the “Format Cells” section. In “Custom,” select h:mm then click “OK” and get the correct value.
example 12
  • Step 13 – Calculate the working hours by summing the cells F2 to F6.
example 13
  • Step 14 – The data is in time format. To change it to number format to calculate the pay, in cell F9, write the below formula.
example 14
  • Step 15 – Repeat the same for overtime hours,
example 15
  • Step 16 – Now, change the format for cells F9 and H9 to number format by right-clicking. Then, in the “Format Cells, click on the “Number.”
example 16
  • Step 17 – For regular working hours, the pay is ₹500, and for overtime hours, the payment is ₹650. First, calculate the pay by the below formula.
example 17
  • Step 18 – Now, start giving the inputs to see the result.
example 18

The total pay received by the employee will be ₹22287.5.

Example #3

Consider the below example showing working hours of Andy in an organization. We can see the date, log in, log out, and lunch timings.

Timesheet Calculator in Excel - Example 3

Now, let us learn how to find the total working hours using timesheet calculator in Excel.

Below are the steps for creating a timesheet calculator in Excel:

First, in cell F1, write an Excel equation.

The total working hours are calculated by the total time spent by a person while deducting the lunchtime taken by the person.

Timesheet Calculator in Excel - Example 3 - working hours

Drag the formula to cell F6 as we will make 5 entries.

Press Enter key.

We can find the total working hours of Andy.

Timesheet Calculator in Excel - Example 3 - Total

Now using AutoFill, we can find the total working hours of Andy as shown in the below image.

Timesheet Calculator in Excel - Example 3 - Fill handle

Likewise, we can use timesheet calculator in Excel.

Important Things To Note

  • Formatting is the key in the timesheet calculator in Excel.
  • We must always use h:mm, i.e., hours and minutes format, to calculate time.

Frequently Asked Questions

1. What are the quarter hour increments for timesheets calculator in Excel?

To preserve compatible time and attendance records, one should make the hourly employee pay in quarter-hour increments with seven minutes or less rounded down and eight minutes or more rounded up.

2. What is the Excel timesheet with pay?

Excel timesheet with pay refers to downloading an Excel timesheet template, creating and inputting details, pay rates, working hours of an employee, evaluation of hours worked type, and saving the timesheet in Microsoft Excel.

3. What is the disadvantage of timesheet calculator in Excel?

The disadvantage of the timesheet calculator in Excel is that there is a chance of human error at some stage, either accidentally or on purpose. But, this may give rise to the probable wastage of hours.

This article has been a guide to Timesheets in Excel. Here, we discuss creating a timesheet calculator in Excel using formulas and examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *