Excel Formula: Calculate overtime pay
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.
|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.|
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:
Press Enter key, the total pay has been calculated.
Then drag the fill handle over the cells you want to calculate the pay.
Calculate the working hours: =(outtime-intime)*24, for instant, =(D5-C5)*24
Calculate the overtime hours: =workinghours-regular, for instant, =E5-F5
- 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.
- Excel Date Function
DATE function can help us to combine year, month and day numbers from separate cells to a valid date.
- Excel EDATE Function
EDATE function adds n months for a date
- Excel MONTH Function
The MONTH is used to get the month as integer number (1 to 12) from date.
- Excel DAY Function
DAY function gets the day as a number (1 to 31) from a date
- Excel YEAR Function
The YEAR function returns the year based on the given date in a 4-digit serial number format.