Timesheet calculator Excel, as the name implies is a method to record timing. In this method, we often add a person’s time when he enters the office, and when he leaves the office to analyze the total time spent on the job. Mainly this tool was made to keep an eye on the employees to calculate the payroll considering their timings in the office.
How to Create Timesheet Calculator in Excel?
The following components are used in the timesheet:
In time
Out time
Break in time
Break out time
You can add these inputs manually to calculate the net hours spent in the office. The “In Time” from “Break-in time” and “Out Time” from “Break out time” is subtracted because break time is not included in it.
Example #1
Using the timesheet calculator Excel let you record the time a person worked on a job. First of all, let’s understand the basic timesheet calculator.
Usually, the Excel timesheet looks like this:
The values of inputs are added manually and then the Excel timesheet calculation with formulas will be applied to calculate net working hours.
Follow these steps:
- Put an Excel equation in cell F1.
- You will see the net working hours are being calculated by the total time a person spent, whereas the lunchtime will be subtracted from the total time.
- Now, drag the formula to Cell F6.
- Add the date and all the other values.
This was the basic timesheet that only calculates the net hours a person spent on a job. Now, let’s learn how to create a professional timesheet calculator Excel.
Example #2
A company often has to calculate the timing of its employees as well as the overtime they have done. At the month’s end, an employer has to analyze the payroll exactly by calculating the timings correctly. Typically, the company has 8 working hours for the $500 pay, whereas the pay with overtime is $650.
Below you can see the format:
- In cell K2, let’s add the typical working hours.
- Here we will be using the Time Excel function to clarify that the total working time is 8 hours, 0 minutes, and 0 seconds. More than 8 hours will be considered overtime.
- The right format is highly crucial in Excel. In cell F2, add the formula and calculate the total time spent by an employee.
- Right-click on the cell and open the “Format Cells”
- Now, select the “General” category given in the “Number” section.
- Open the “Custom” option and choose “h: mm” for house and minutes.
- Drag it to cell F6.
In the Regular column, you can see that 8 hours a day is the working time. If the total hours of working exceed 8 hours, then it would display the total working hours, if not the regular eight hours. IF function will be used here, in cell G2, you can add the timesheet Excel formula:
Cell K1 is locked because there is no change as a reference.
- Drag cell G2 to G6.
- You can see the value is not correct in cell G2 because the formatting is not right. put the right value by clicking on cell G2. Press right-click and open the “Format Cells”, choose the “h: mm” in the “Custom” section, and press “OK.”
- Now, pick Excel format painter brush to copy the similar format for the below cells.
- Now, calculate the overtime of an employee. Type the Excel timesheet formula in cell H2:
- You can now calculate the overtime of an employee.
- Drag this to cell H6.
- Always consider entering the right format. Right-click on the selected cells and open the “Format Cells” option. Choose h: mm from the “Custom” and click “OK”.
- Calculate the working hours by adding cells F2 to F6 values.
- The data appears in time format and you need to change it. For this, calculate the pay by adding the below-given formula in cell F9.
- Repeat the process for overtime hours.
- Let’s change the format for cells F9 and H9 to number. Right-click on it and choose “Number” from the “Format Cells” option.
- For regular working hours, the pay is $500 and with overtime, the pay is $650. Calculate the pay with the following formula:
- Add inputs and get the results.
Things to Consider
In Timesheet Calculator Excel, formatting is the key point.
To calculate time, you need to use h: mm for the hours and minutes format.
That’s It
You are now capable to create your timesheet calculator for the employees. Or you can even make one for yourself as well so that you can have an idea about your monthly income before it comes into your hands. Be clever enough to learn skills because Excel has many wide openings to explore.
