To get the Total Pay, we use the above formula in Cell H6 like this:
=(F6*H6)+(G6*H6*1.5)
To breakdown the formula, (F5*H5) calculates the pay for the regular hours and (G5*H5*1.5) calculates the pay for working overtime. By adding these two, we get the total pay.
First of all, we’ll need to calculate the total working hours by using the Total Working Formula (mentioned in other formulas) and putting it in Cell E6:
=(D6–C6)*24
As we know, Excel calculates time as fractions of a day, such as 6:00 AM is 6/24=0.25, 12:00 PM is 12/24=0.5.
That’s why, we multiplied the difference of Time In and Time Out by 24, to convert the result into decimal hours. After calculation, Excel may display the total working hours, using a time format like h:mm.
To correct this, simply change the format of Column E to General format from the Home Tab.
After calculating the total working hours, the regular and overtime working hours need to be separated. For this, we’ll calculate the regular working hours by using the following formula in Cell F6:
=MIN(8,E5)
The MIN function will display the smallest number of the provided options. Here, it will display either 8 or total working hours, whichever is the smallest value.
The overtime hours are calculated by subtracting the regular working hours from total working hours. The formula is put in Cell G6:
=(E5–F5)
If we just want to calculate the overtime pay, we can do that using this formula:
=G6*(1.5*$C$4)
The basic overtime pay calculation formula works exactly the same in Google Sheets as in Excel:
