Here is one of the best overtime calculation formulas that you can expect. I am sure that no other formula can match the formula that I am providing you here! Yes, I am not exaggerating.
When someone works beyond his normal working hours, he gets paid for that additional time. That depends on the prevailing rules and regulations in his country. Let’s leave that aside.
What you want to learn is how to calculate overtime hours and payments. What I am going to provide you is one of the best overtime calculation formulas to find the overtime hours that someone worked.
Once you find the OT aka overtime hours, you can multiply it with the agreed overtime rates per hour. So overtime calculation will be no more a headache for you!
The below dynamic overtime calculation formula is for Google Sheets users. I am sure that the formula that you are going to get is one of the best for calculating overtime hours. I will tell you why.
In the above example, I’ve given two employees’ overtime start and end time in 24 as well as 12 hours format.
My formula would work well in both these formats. Also, please see the overtime end time of Employee B.
He started and ended his overtime work on two different days. That means he worked after midnight.
My simple elegant formula addresses this issue too without any extra steps. Get that much-hyped formula below.
The Best Overtime Calculation Formula in Google Sheets
Generic Formula:
=MOD(overtime end time - overtime start time,1)In the above example (see the screenshot), I’ve entered the below formula in cell D3 in line with the above generic formula and then copied to the cells D4, D8, and D9.
=mod(C3-B3,1)This MOD formula works well on 12 hours, 24 hours format and also works correctly with midnight hours. No need any additional changes.
As you can see the output is in time duration. For example, in cell D3, the formula returns 4:30:00. That means 4 hours 30 minutes.
If the format (over time calculation) is not in time duration, select the range D3:D9 and apply Format > Number > Duration.
How to Calculate Overtime Amount from Time Duration?
To calculate the OT (overtime) amount use the formula as below.
In any other cell, probably in cell E3, you can try this formula.
=D3*24*OT RATEAlso, you can nest the above formula with the MOD formula itself in cell D3 as below.
=mod(C3-B3,1)*24*OT RATEIf you think this’s the best overtime calculation formula that you have come across in Google Sheets, don’t forget to share this post with your friends. Also, please like my social pages to get the latest content updates.
Related Formulas:
Hello,
I’m trying to create a formula for a timesheet. I would like to have an “hours worked” section. In that section, you can add any number. Anything over 40 hours, would be overtime.
Three Boxes: Hours Worked – # of regular hours worked – # of overtime hours worked.
I’m stumped to all heck. Any advice? Thanks
Hi, Kyle,
The formula may depend on many factors. I don’t know what values you have in your columns and in which format. So, if possible, please share a sample sheet URL in the reply. Show me your result also.
Hi,
Thanks so much for the quick reply! I’m sure it’s rather simple but I’m lost. Hope this link works.
— link removed from this thread by the admin —
Hi, Kyle Gregory Arnhart,
Thanks for the sample sheet.
I could see that cell C4 contains the total hours worked.
You want to split it into two parts, i.e., the regular hours worked in cell C5 and the overtime hours worked in cell C6.
You can use the below two IF statements.
In Cell C5:
=if(C4>40,40,C4)In Cell C6:
=if(C4>40,C4-40,0)I hope this helps?
How do I calculate total OT worked from total Hours worked… time going above 40 hours (total hours worked is in Column J6 in case anyone is looking to write the formula out).
Hi, Mika,
You can try the below combo formula.
=max(0,value(J6)-value("40:00:00"))If it’s (the formula is) in cell J7, format it to duration (Format > Number > Duration).
The formula is great, but why the mod function works here. What’s the catch, how it works exactly?
Hi, Domagoj,
See the output of the following formulas.
=mod(1,1)result 0
=mod(-0.99,1)result 0.01
The formulas return the remainders 0 and 0.01 respectively after dividing the number 1 in the first example with 1 and -0.99 in the second example with 1.
Now you may insert the below formula in cell H3 and copy to H4, H8, and H9 then format it to number (Format > Number > Number).
=C3-B3In the cell I3, insert the below formula and copy as above to I4, I8, and I9.
=mod(H3,1)Format these values also to number. You will understand how the formula works and why or how we can use the MOD formula as one of the best overtime (OT) calculation formula in spreadsheets.
You could just do it this way and change the multiplier if you needed to.
(i.e. for double time, put 2 instead of 1.5)
=IF('hours'40,40*'StdPay'+('hours'-40)*('StdPay')*1.5))))You’ll find that this could work in almost any scenario where you have at least the 2 necessary variables;
Hours and Standard Pay Rate.