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:
- In time
- Out Time
- Break-In Time
- 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.
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,
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 –
- 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,
Now we have our first timesheet, which calculates the total hours spent by a person on a job.
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..
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,
- Step 1 – First, let us input the standard working hours in cell K2,
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. 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.
- 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.
- 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’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,
I have locked the 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 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.
- Step 9 – Use 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. 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,
This 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, go to the format section, and in custom select ,h:mm then click ok and get the correct value.
- Step 13 – Calculate the total working hours by summing the cells F2 to F6.
- 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.
- 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. F9 and H9 to number format by right click, and in the format cells, click on the number.
- 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,
- Step 18 – Now, start giving the inputs to see the result.
The total pay received by the employee will be 22287.5 INR.
Things to Remember
- Formatting is the key in the timesheet calculator in excel.
- Always use H:mm, i.e., hours and minutes format to calculate time.
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 –