Generally, overtime pay is different from the regular working pay, for instance, 10 dollars per hour for regular working hours, but 15 dollars per hour for working overtime. Here in this tutorial, it shows a timesheet to list the working hours, and provides a formula to calculate the overtime pay.
If you want to follow along with this tutorial, please download the example spreadsheet.
Generic formula:
(reg_hrs*rate)+(ot_hrs*rate*1.5)
Arguments
Reg_hrs: the number of regular working hours.
Ot_hrs: the number of overtime working hours.
Rate: regular hourly pay.
1,5: 1.5 times the normal rate. Here taking into account overtime paid at 1.5 times the normal rate. You can change it to the actual value.
Return value
It returns the total pay including regular working pay and overtime working pay.
How this formula work
Supposing the timesheet is shown as above, please use the formula as this:
=F5*H5+(G5*H5*1.5)
Press Enter key, the total pay has been calculated.
Then drag the fill handle over the cells you want to calculate the pay.
Note
Calculate the working hours: =(outtime-intime)*24, for instant, =(D5-C5)*24
Calculate the overtime hours: =workinghours-regular, for instant, =E5-F5
Relative Formulas
Add Hours Minutes Seconds To Time Supposing there is a list of time values in Excel, now you want to add 3 hours 45 minutes and 20 seconds to the time values, Excel provides two formulas to help you.
Extract Or Get Date Only From The Datetime In Excel To extract only date from a list of datetime cells in Excel worksheet, the INT, TRUNC and DATE functions can help you to deal with this job quickly and easily.
Add Minutes To Time This tutorial provides formulas and explains how to add minutes to time in Excel.
Excel Formula: Add Months To Date It introduces formulas to add months to a given date in Excel, and explains how the formulas work.
Relative Functions
Excel Date Function DATE function can help us to combine year, month and day numbers from separate cells to a valid date.
