Excel Timesheet Calculator

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

The timesheet uses the following inputs from the user:

  • In timeOut TimeBreak-In TimeBreak 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.

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:

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.

Example #2

Let us professionally make a timesheet.

  • 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. Drag the formula to Cell F6 as we will make 5 entries. Now, input the date and the rest of the values. Our first timesheet calculates the total hours a person spends on a job.

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

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

For a company to calculate the time worked by its employees and the overtime time that 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. For the mandatory 8 hours, the pay is ₹500, while for overtime, the pay is ₹650.

The format is as below:

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

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

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

  • Step 4 – Go to “Custom” and select “h:mm” for hours and minutes.

  • Step 5 – Drag it to cell F6.

  • 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:

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

  • Step 7 – Drag cell G2 to G6.

  • 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.”

  • Step 9 – Use an excel format painterExcel Format PainterFormat painter in Excel is a tool used to copy the same format of a single cell or a group of cells to the other cells. You will find it on the home tab in the clipboard section.read more brush to copy the same format for the cells below.

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

It will calculate the overtime done by an employee.

  • Step 11 – Drag this to cell H6.

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

  • Step 13 – Calculate the working hours by summing the cells F2 to F6.

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

  • Step 15 – Repeat the same for overtime hours,

  • Step 16 – Now, change the format for cellsFormat For CellsFormatting cells is an important technique to master because it makes any data presentable, crisp, and in the user’s preferred format. The formatting of the cell depends upon the nature of the data present.read more F9 and H9 to number format by right-clicking. Then, in the “Format Cells, click on the “Number.”

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

  • Step 18 – Now, start giving the inputs to see the result.

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

Things to Remember

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

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: –

  • Create Excel Time Card TemplateCreate Excel Time Card TemplateManagers and owners can use an excel time card template to keep track of their employees’ and workers’ attendance, leaves, and efficiency.read moreVBA TimeValueVBA TimeValueVBA’s Time Value function is part of the date and time category, and it returns the numerical value of the date provided in as an argument.read moreTime Tracking TemplateTime Tracking TemplateA time tracking template helps keep the organization track of the hours dedicated by its employees during a designated period.read moreTimeline Chart in ExcelTimeline Chart In ExcelIn project management terms, an Excel timeline chart is also known as a Milestone chart. It is used to visually track all stages of a project and assists a project manager in delivering the project to the end-user as committed before the start of the project.read more