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.
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. - 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.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.