Calculating Overtime without going over 12 hours

[ljmparalegal]

I am working on spreadsheets to determine regular hours (8 hours or less), overtime hours (8-12 hours), and double time hours (12+ hours).

I am having trouble figuring out how to calculate the overtime hours without either getting a negative number when the hours are less than 8, or causing it to calculate all hours over 8, including hours over 12, which should actually be double time.

Basically I am looking for a formula that would state: If the hours worked are more than 8 but less than 12, the result is regular pay rate*1.5, and if the hours worked are less than 8, the result is 0.

Any help would be greatly appreciated!

Note: I already have the hours worked as a number, so I don't need to multiply anything by 24 (as I know sometimes that is part of a formula to ensure the correct amounts).

Thanks!

[FDibbins]

Hi, welcome to the forum

Note: I already have the hours worked as a number, so I don't need to multiply anything by 24 (as I know sometimes that is part of a formula to ensure the correct amounts).

Actually, it is often better to leave time as TIME, and not "convert" it. It already is a number - Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75.
So if you are testing for 8 hr's, you are testing for 8/24.
Testing for 12 hrs would be 12/24 or 0.5
etc

[jason.b75]

Welcome to excel forum.

Assuming that you have total hours worked in A2

To get regular hours use =MEDIAN(0,+A2,8)
Overtime =MEDIAN(8,+A2,12)-8
Double time =MEDIAN(12,+A2,24)-12

edited in line with comments in post #7

[Pete_UK]

Assuming the hours worked are in A1, then:

Regular pay = MIN(A1,8) * 1.0 * hourly_rate

Overtime pay = MIN(4,MAX(A1-8,0)) * 1.5 * hourly_rate

Double-time pay = MAX(0,A1-12) * 2.0 * hourly_rate

Hope this helps.

Pete

[ljmparalegal]

Pete,
This is working perfectly, thank you so much!

Would you mind explaining why I need the 4 before the MAX on the overtime pay? I basically have to explain to my boss how these are calculated.

Thanks again!

[Pete_UK]

Well, you need to understand the MAX term first - imagine A1 is 6, then that would give MAX(-2,0) resulting in 0. If A1 is 7 it will also result in 0, as would A1 being 8. However, if A1 is 9, then the term becomes MAX(1,0), resulting in 1. If A1 is 10 then the result is 2. So, the MAX term is giving the (total) number of hours above 8, or zero if A1 is 8 or less - it is similar to doing:

IF(A1<=8,0,A1-8)

But, for time and a half hours, you don't want it to exceed 4, so the MIN term is effectively MIN(4,total_overtime_hours). If the total overtime hours is 5, say (worked 13 hours in total), then the MIN function will return 4. However, if the total overtime hours is less than 4 (worked from zero to 12 hours), then the MIN term will return whatever the total overtime hours is.

Hope this helps.

Pete

PS. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[jason.b75]

I basically have to explain to my boss how these are calculated.

Maybe have a look at my suggestion instead, it uses a simple math principle that the average 6 year old would probably understand, your boss should be too embarrassed to ask you to explain that one

One point to make though, if there are no hours entered it will show some obscure numbers, purely because the median function doesn't recognise empty cells as 0 so it averages the other 2 values, simply inserting a + sign in front of A2 in each of the formula will fix that. I've edited the formula in my original post to show this.