# Calculating Overtime without going over 12 hours

1. ## Calculating Overtime without going over 12 hours

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!

2. ## Re: Calculating Overtime without going over 12 hours

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

3. ## Re: Calculating Overtime without going over 12 hours

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

4. ## Re: Calculating Overtime without going over 12 hours

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

5. ## Re: Calculating Overtime without going over 12 hours

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!

6. ## Re: Calculating Overtime without going over 12 hours

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

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

7. ## Re: Calculating Overtime without going over 12 hours

Originally Posted by ljmparalegal
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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1