You are here:

Excel/OT AND Double time calculations


QUESTION: Hello! I'm using Excel 2010. I need to enter a formula that will calculate OT(overtime) for daily hours worked over 8 but I need to pay Double time for daily hours worked over 12.  How do I allow for that? I'm thinking one row for OT and one row for double time.  I've entered =IF(SUM(G1:G2)>8,SUM(G1:G2)-8,0)to calculate the OT on the 15 regular hours (below sum of G1 and G2)but I'm not going to pay "7" at the OT rate.  I'm going to pay only 4 at the OT rate, the other 3 hours must go at the double time rate.  The formula in the "DOT"(double time) row is currently =IF(SUM(G3)>4,SUM(G3)-4,0) but that doesn't work if I reduce the OT row to accurately reflect "4".

Daily total hours will be entered in the "REG" rows (some people may have only one REG row, those with different depts. will have 2 or more) and the OT row should only show hours totaling over 8 through 12.  Anything over 12 should calculate in the "DOT" row.  Please and THANK YOU!!

REG      10.00
REG      5.00
OT      7.00
DOT      3.00

ANSWER: Elena,

OT:  =IF(SUM(G1:G2)>8,Min(SUM(G1:G2)-8,4),0)

DOT: =IF(SUM(G1:G2)>(8+4),SUM(G1:G2)-(8+4),0)

Those worked for me.  You can adjust the reference to Sum(G1:G2) to reflect how the REG hours are to be entered

Tom Ogilvy

---------- FOLLOW-UP ----------

Sample spreadsheet
Sample spreadsheet  
QUESTION: Thank you SO much for your quick response Tom!  Your formulas work perfectly.  But as I go along, I need more help.  I've entered your formulas in the OT and DOT rows but I'd like the row named "TRUE REG" (for now) to display the total regular hours. For example, on the 11 hour workday, it should calculate "8".
Also, I'll need to change my formulas in the week one and week two columns to calculate any "weekly OT".  These calculations are for our new California branch. CA labor laws state:
OT for anything over 8 hrs in one day
OT for anything over 40 hrs of reg time for that work week
(no "pyramiding of time, so once an hour is paid as OT either daily or weekly it cannot be counted again for OT)
Double time for daily hours over 12
Double time on the 7th consecutive day of work for any time over 8 hours worked.
I've entered a seemingly crazy scenario I found.  Total of 81 hours in one week.  If we follow the rules, the spreadsheet should calculate: 40 hrs of reg time, 32 OT and 9 hrs of Double time.  Can you help with this?  It is GREATLY appreciated!


You picture is too small to make out any details.  Why not send me a sample spread sheet and mark where you want the formula and how they should act.  You might put in the correct and answer on the sheet as well. If I have questions, then I can ask them of you.

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.