How to calculate working hours based on the timesheet in Excel
How to calculate working hours based on the timesheet in Excel: In a company, the accounting department is responsible for managing the working hours of all employees to calculate their salaries. Usually, the timesheet provides data for the time in and time out to calculate working hours. So, how can you get accurate and fast calculation results? Read the article below to learn how to calculate working hours in Excel (through functions and tricks).
For example, we have a timesheet as shown below. We need to calculate the daily working time for each person.
1. Calculate working hours by subtracting the time out from the time in
The simplest way to calculate working time on the timesheet is to subtract the time out from the time in. In cell D2, enter the following formula:
=C2-B2
Copy the formula for all remaining cells to get the results.
How to calculate working hours based on the timesheet in Excel
2. Calculate working time using functions
With this method, we need to use two functions: HOUR and MINUTE. The structure of the two functions is as follows:
a. HOUR function
Function syntax: =HOUR(serial_number)
Where: serial_number: Required argument, the time containing the hour you want to extract.
The HOUR function is used in Excel to return the value of the hour in the Excel cell. The hour value is returned as an integer from 0 (12:00 AM) to 23 (11:00 PM).
b. MINUTE function
Function syntax: =MINUTE(serial_number)
Where: serial_number: Required argument, the time containing the minute you want to extract.
The MINUTE function is used in Excel to return the value of the minute in the Excel cell. The minute value is returned as an integer from 0 to 59.
Applying the structure of the two functions above, we have the formula to calculate working time on the timesheet as follows:
=(HOUR(time out)*60-HOUR(time in)*60+MINUTE(time out)-MINUTE(time in))/60
In cell D2, enter the following formula:
=(HOUR(C2)*60-HOUR(B2)*60+MINUTE(C2)-MINUTE(B2))/60
Copy the formula for the cells below. Then, select all result cells, and choose the General format in the Number section.
How to calculate working hours based on the timesheet in Excel
The results of the working time for all employees will be as follows:
How to calculate working hours based on the timesheet in Excel
Therefore, the above article has guided you on how to calculate working time on the Excel timesheet. Hopefully, this article will be useful for you in your work. Wish you success!
