Buzz
With simple formulas, you can determine the work hours of multiple employees by providing their start and end times of tasks.
There are two methods to calculate work hours in Excel.
1. Calculate work hours in Excel by direct subtraction.
Utilize the direct subtraction method by subtracting the end time from the start time to calculate work hours in Excel:
=end time – start time
When using this calculation, the cell containing your result will automatically convert to the time data type.
Simply copy the formula down, and you can calculate work hours for other tasks.
When your parameter is set to 10 hours 17 minutes, the result of the HOUR function will be 10 hours.
Similarly, using the formula MINUTE(parameter), the MINUTE function will give you the result in minutes for the parameter.
For example, if your parameter is set to 10 hours 17 minutes, the result of the MINUTE function will be 17 minutes.
Based on these two formulas, we can use various calculations to determine the number of hours based on the conversion principle of the total hours or minutes when finishing work and subtracting the total hours or minutes when starting work (because the end time is always greater than the start time). However, it is important to note that you choose a consistent approach to convert calculations to either hours or minutes to obtain accurate results.
This leads us to some formulas as follows:
=(HOUR(departure time)*60+MINUTE(departure time)-HOUR(entry time)*60-MINUTE(entry time))/60
In this calculation, we convert everything to minutes (so after each HOUR, you must multiply by 60 to calculate minutes), and finally, we divide the total minutes by 60 to determine the number of hours.
=HOUR(departure time)+MINUTE(entry time)/60-HOUR(entry time)-MINUTE(entry time)/60
In this calculation, we convert everything to hours, so after each MINUTE, we must divide by 60 to get the final result in hours.
=HOUR(departure time)-HOUR(entry time) + (MINUTE(departure time)/60-MINUTE(entry time)/60)
This calculation converts everything to hours, so after each MINUTE, we must divide by 60 to get the final result in hours.
=(HOUR(departure time)*60-HOUR(entry time)*60 + (MINUTE(departure time)-MINUTE(entry time))/60
In this calculation, we convert everything to minutes (so after each HOUR, you must multiply by 60 to calculate minutes), and finally, we divide the total minutes by 60 to determine the number of hours.
To achieve the desired final result, you need to change the format of the result cell. It automatically converts to an hour format rather than a numerical format. Click and select the result data cell, click on the Home ribbon, and choose the General format in the Numbers group.
The result you will receive is the number of working hours based on the numerical result:
This tutorial on calculating working hours from Mytour concludes here. We hope that through this article, readers can learn different ways to calculate working hours in Excel.
