Timesheet Calculator in Excel

Excel Timesheet Calculator

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

How to Create Timesheet Calculator in Excel? (with Examples)

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

These inputs can be manually entered by the user 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

Example #1

Timesheets calculator in excel is used to record the amount of time a person worked on a job. In the first example, we will learn about the basic timesheet, and then we will move to a professional one.

The format of the excel timesheet looks like this,

Time sheet in Excel example 1

The values in Date, Time in-out, lunch in-out will be manually entered by the user. And apply the excel timesheet calculation with formulas in Total working hours will be calculated.

Below are the steps for creating timesheet calculator in excel –

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

    Now we have our first timesheet, which calculates the total hours spent by a person on a job.

Example #2

Let us make a timesheet in a professional way.

For a company to calculate the time worked by its employees and overtime time has been done, it needs to calculate the payrollPayrollPayroll refers to the overall compensation payable by any organization to its employees on a certain date for a specific period of services they have provided in the entity. This total net pay comprises salary, wages, bonus, commission, deduction, perquisites, and other benefits.read more.

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

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

I have used the TIME excel functionTIME Excel FunctionTime is a time worksheet function in Excel that is used to calculate time based on the inputs provided by the user. The arguments can take the following formats: hours, minutes, and seconds.read more so that the hours are 8 hours, 0 minutes, and 0 seconds. Anything above that will be considered as 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 – Now, we want our cell to be in the correct format, so right-click on the cell and go to the format category.
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’s say if the total number of working hours exceeds eight hours a day, then it should display the total working hours if not display the regular eight hours.

For this, we use an if function. In cell G2, write the excel timesheet formula,

TimeSheet in Excel example 6

I have locked the 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 the formatting is incorrect.

So now, to get the correct value, we need to click on Cell G2, and press right-click, then go to the format cell 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

This 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, go to the format section, and in custom select ,h:mm then click ok and get the correct value.
example 12
  • Step 13 – Calculate the total working hours by summing the cells F2 to F6.
example 13
  • Step 14 – The data is in time format, and 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
example 16
  • Step 17 – For regular working hours, the pay is INR 500, and for Overtime hours, the pay is INR 650. 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 INR.

Things to Remember

  1. Formatting is the key in the timesheet calculator in excel.
  2. Always use H:mm, i.e., hours and minutes format to calculate time.

Recommended Articles

This has been a guide to Timesheet in Excel. Here we discuss How to Create Timesheet Calculator in Excel using formulas and examples along with downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion

Reader Interactions

Leave a Reply

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