The concept of time as a fraction and an important variable in the calculation of overtime must first be understood before we can press any further. Note that excel deals with time as fractions of a day. Such that 12.00pm is .5, 6.00am is 25, 6.00pm is .7. Most times, you may need to convert time to decimal hours in order to make the calculations straightforward. To calculate overtime and pay associated with the overtime, you need to understand the formula which is a combination of hours and rate.
=(reg_hrs*rate) + (ot_hrs*rate*1.5)
Let’s go through each argument.
- Reg; regulation time is always constant
- Ot; Overtime which is a variable
- Rate; monetary valued tied to each overtime
The formula above can be used to calculate overtime and the amount to be paid for every extra hour worked by an employee, for instance.
Let us assume we want to calculate the total overtime hours spent by employees in a biscuit factory.
Normal working hours must first be calculated. See example below in the column E which will house all the values associated with hours worked.
Figure 1. Excel showing calculation of normal working hours
The next line of action in our example will be to calculate regular time. To do this, we use the formula:
= MIN (8, E5)
Knowing the regular time will help us in calculating the number of hours spent working overtime.
Figure 2. MIN function returns the regular hours spent by each worker.
Now, we can calculate the actual overtime spent by each employee, since we have all our parameters set. This is simple to do, however. We just do a subtraction of the regular hours from the total number of hours spent at work.
=E5 – F5
From our example, our results show that only 3 workers did an overtime. The rest with zero either spent the total regular time or less than it.
Figure 3. Excel showing the number of hours spent on overtime.
Here, we do a calculation of the total overtime spent. Which is why we’re here in the first place. The formula considers the normal rate, hours, and then for this example, the overtime rate, which we set at 2.5.
Our normal rate also stands at $30.
Applying the formula, =(F5*H5) + (G5*H5*2.5), gives us a total overtime rate of $390.
Figure 4. Excel shows the total overtime calculation for Phil.