Calculate Overtime in Excel & Google Sheets
Written by
Reviewed by
Download the example workbook
This tutorial will demonstrate how to calculate overtime pay in Excel & Google Sheets.
Calculate Total Pay
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.
Calculate Total Working Hours
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.
Calculate Regular Working Hours
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.
Calculate Overtime Working Hours
The overtime hours are calculated by subtracting the regular working hours from total working hours. The formula is put in Cell G6:
=(E5–F5)
Just Overtime Pay
If we just want to calculate the overtime pay, we can do that using this formula:
=G6*(1.5*$C$4)
Basic Overtime Calculation in Google Sheets
The basic overtime pay calculation formula works exactly the same in Google Sheets as in Excel:
