Excel has been helping HR managers for years in tracking the working hours of employees in their organizations. You can calculate the total working hours using operators including subtract and multiply, or if you’re calculating work hours for a night shift, you can use the MOD function.
In this article, we have covered three cases where you may have to calculate the total working hours. Including an Excel formula, we have also included the VBA code to create a function to calculate the hours for all of the discussed cases.
Case 1: Calculate Working Hours for Day Shifts
In this spreadsheet, we’re calculating the total working hours of ten workers employees in a company. If you notice, the clock-in time for all employees is in AM while the clock-out time is in the PM time format. We will base our formula using this information.
To calculate the total hours worked by an employee in a day, we entered the following formula in cell F4:
=(E4-D4)*24
In this formula, we subtracted the clock-out time from the clock-in time to get the value between the hours. Then, we multiplied the subtracted value by 24 to convert the decimal values into hours.
Use VBA
You can also create a custom function to calculate the total working hours by entering the time of clock-in and clock-out. Enable the Developer tab and select the Visual Basic option. Right-click on your sheet from the sidebar and insert a new module. On your right, enter the following code:
Function TotalHours(Clock_in As Date, Clock_out As Date) As Date
Result = (Clock_out - Clock_in) * 24
TotalHours = Result
End Function
You can then use the TotalHours function like you would use any other function in Excel. As your arguments, enter the clock-in and clock-out time in order and hit enter.
Case 2: Calculate Working Hours for Night Shifts
The formula to calculate working hours for night shifts is a bit different than calculating hours for day shifts. This is because the clock-in time is greater than the clock-out time. When you subtract the clock-out time from the clock-in value, the return value will be negative.
To workaround this problem, we will be using a different formula with the MOD function. The MOD function takes in two arguments, the number and the divisor. When we divide a negative value by 1, the remainder value will return a positive number.
Here is the formula we entered in cell F4 of our worksheet:
=MOD(E4-D4,1)*24
Here’s how the formula works. The return value after subtracting E4 from D4, the MOD function will divide it by 1. For instance, if the subtracted value is -0.66, dividing it by 1 will give 0.66 as the remainder value. Then, multiplying the date value by 24 will convert the decimal value into hours; in this case 16 (rounded off).
Use VBA
You can use the same logic to create a function using the VBA code:
Function NightShift(clock_in As Date, clock_out As Date) As Integer
c = -(clock_out - clock_in) * 24
Result = 24 - c
NightShift = Result
End Function
Case 3: Calculate Overtime Hours
In this case, we will be calculating overtime hours worked. For this, we will be using a condition using the IF function. IF is a conditional function that performs a command if the set value is true. Additionally, you can also enter what action to perform when the criteria are false.
Let’s calculate the overtime hours for the employees in this data table. The normal work time for this company is 8 hours. Anytime over 8 hours will be considered overtime. We will be using the IF function to apply this condition in the following formula:
=IF(F4>8,(F4-8),"NA")
Cell F4 holds the total working hours. If a cell is over 8, the normal working time, the value in F4 is subtracted by 8, giving us the total overtime value in hours. If this criterion is not met, the formula will return “NA”, meaning that overtime is not applicable.
Use VBA code
Here’s a VBA code you can run to calculate overtime in Excel:
Function Overtime(hour As Integer, regular_hrs As Integer) As Date
Dim Result As Integer
If hour > regular_hrs Then
Result = hour - regular_hrs
Else: Result = 0
End If
Overtime = Result
End Function