# Excel: Complicated Overtime Formula Help

Discussion in 'Business Applications' started by Sarahml, Jan 10, 2013.

Thread Status:
Not open for further replies.
Advertisement
1. Hi,

I'm in charge of calculating overtime where I work and at this point it is all done manually.

The more I learn about excel, the more I realize how powerful it is, so I'm wondering if someone can let me know if this is possible or not:

Our overtime is calculated based on:
• The empoyee will receive 1.5 times the hourly rate for time worked over 8 hours per day or 40 hours per week (whichever is greater) and 2.0 times for time worked over 12 hours per day.
I don't need to figure out rates of pay, or total pay, but I do need to figure out how many overtime hours the person worked.

So for example:

Billy worked:

Monday - 8 hours
Tuesday - 8.5 hours
Wednesday - 9 hours
Thursday - 7 hours
Friday - 6 hours

Total hours: 39.5
Total hours over 8 hours per day or 12 hours per day: 1.5

Total hours were less than 40, so 1.5 hour is greater, so that's the number I would use.

Sally Worked:

Monday - 9 hours
Tuesday - 14 hours
Wednesday - 8 hours
Thursday - 8 hours
Friday - 8 hours

Total hours: 47
Total hours over 8 hours per day (7) over 12 hours per day (2) = 9 overtime hours
9 hours is greater than (47-40) so I would use 9 hours

I know this is complicated and time consuming. I've donated to the forum before and would be happy to again if anyone can help.

Thanks! 2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
can you show the format in a dummy spreadsheet

I suspect an array formual a sumproduct may work - but still working that out

BUT if you add an extra column to calculate the hours over 8 and 12 using IFs - and then use a Sum to add those up

dont understand this bit

as I say maybe put this into a spreadsheet - how it would be setout and see what we can do

see my example
I have put the Person in the Rows and the Days in columns
Code:
```		Hours Worked						Hours worked over 8 & 12						Total Hours
Monday	Tuesday	Wednesday	Thursday	Friday		Monday	Tuesday	Wednesday	Thursday	Friday
Billy worked:		8	8.5	9	7	6		0	0.5	1	0	0		1.5
Sally Worked:		9	14	8	8	8		1	8	0	0	0		9
```
and used the IF statement
=IF(C3>12,(C3-12)+(C3-8),IF(C3>8,(C3-8),0))

to calculate each day

see sample attached
- but someone more knowledgeable - may be able to simplify

#### Attached Files:

• ###### overtimehours_etaf.xlsx
File size:
10.7 KB
Views:
62
3. Thanks for the fast reply!

I've attached my example spreadsheet:

The light green columns are columns I've added myself to try and help show what I need done in terms of an excel formula. The calculated overtime hours column is done manually as of right now and is very time consuming considering I have to do this for 700+ employees each month.

What I meant by "9 hours is greater than (47-40) so I would use 9 hours" is that the calculated overtime hours is 9, which is greater than the number of hours greater than 40, which is 7. So I would use 9 hours as that is larger than 7 hours.

Thanks again for your help!!

4. Sorry forgot the attachment!

File size:
11.2 KB
Views:
41
5. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
so I have added a section column L - O which calculates the values automatically - you have in the Green highlight area
In order to work out the hours over 8 / 12 , I have added a helper section in columns S - W which is grey

Now your column J - my column O - orange - I dont know what the criteria is for this

AND I'm really sorry , I still dont get this bit
9 hours is not greater than 47-40 , and so sorry lost that bit of info

see attached - make some changes and see if it works as expected in my bit - or at least for the bit i understand

File size:
13.1 KB
Views:
33
6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
Ok- re read that bit a few times

47-40 which is 7
9 is greater than the 7 - so that means you used 9

if the result was 6
which is not greater than 7 - what would you use

any reason for the 47 , would that 7 apply to all calculations

just need those scenarios to be able to formulate

7. Please see my notes in red, and thanks again! 8. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
so the rule would be

If overtime hours calculated in your Column I my Column N is greater than zero BUT less than or =7 ,then use 7

if over 7 then use the number calculated

so in you column overtime to be used - None are over 7 hours - so shouldn't you use 7 - which is greater

why are the numbers the same as the overtime hours actually worked

I'm really sorry , i'm not getting this bit

can you perhaps modify my spreadsheet I posted so that the hours worked , fall into the categories over 7 and under 7

and show what you what to happen

OR if you could describe the rule(s) logically

like

if hours over 40 but under 47, then the overtime would be 7 hours in all cases
if over 7 hours overtime worked - then use the hours worked

we have not included weekends in the spreadsheet - would all hours worked sat and sun be overtime - we can add those columns

9. Hi Etaf,

I think somewhere along the line I've confused you with "7 hours" 7 hours doesn't mean anything, it was just an example.

The rule is "Employees are eligible to receive 1.5 times the hourly rate for time worked over 8 hours per day or 40 hours per week (whichever is greater) and 2.0 times for tiem worked over 12 hours per day.
• If a person works more than 8 hours a day, anytime afterwards is considered overtime and is paid at time and a half
• If a person works more than 12 hours a day, 8 hours are paid at straight time, 4 hours at time and a half, and anything over 12 is paid at double time.
• For each week block (Sunday to Saturday) add up all the hours worked
• If the total hours worked is greater than 40 those would be overtime eligible
• If the total hours worked is greater than 40, then do a calculation to figure out overtime (Total Hours (less) 40) we'll call this Total Overtime
• If Total Overtime is less than Calculated Overtime use the Calculated overtime
• If Total Overtime is more than Calculated Overtime use the Total Overtime
Example of how Calculated overtime is determined

If John works 8 hours on Monday - Calculated Overime would be 0
If John works 9 hours on Monday - Calculated Overtime would be 1
If John works 12 hours on Monday - Calculated Overtime would be 4
If John Works 14 hours on Monday - Calculated Overtime would be 8 (6 hours at time and half and 2 at double time)
If John Works 16 hours on Monday - Calculated Overtime would be 10 (6 hours at time and a half and 4 at double time)

Hopefully that helps? I know it's complicated! Sorry...

10. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
itshould be all possible to do - just may take a little time now getting in perfect

Thanks , Thats useful

so my calculations are not valid - as I added over 8 and over 12 together and you need to calculate those at a different rate

But only if they work more than 40 hours in the week
so on Monday John works 16 Hours - but Tues,wed 8hours and thur and fri OFF = 16+8+8 = 32 hours - so NO overtime paid
scenario 2 - John works Mon = 16, tue = 8, wed = 8 Thur = 8 Fri = 1 total week 41 hours - what happens ? 1 hour @time and half

so the hours actually need to change to reflect that assume 14hrs
[email protected]*1.5 and 2 @ *2 = so the hours should show 6 + 4 = 10hours

so the actual hours you need to show in the overtime for Payment would be
If John works 8 hours on Monday - Calculated Overime would be 0
If John works 9 hours on Monday - Calculated Overtime would be 1 @ 1/5 - so Hours = 1*1.5 = 1.5
If John works 12 hours on Monday - Calculated Overtime would be 4@ 1/5 - so Hours = 4*1.5 = 6
If John Works 14 hours on Monday - Calculated Overtime would be 8 (6 hours at time and half and 2 at double time) @ 1/5 - so Hours = 6*1.5 = 9 + @ 2 - so Hours = 2*2 = 4 - Total = 13
If John Works 16 hours on Monday - Calculated Overtime would be 10 (6 hours at time and a half and 4 at double time)
@ 1/5 - so Hours = 6*1.5 = 9 + @ 2 - so Hours = 4*2 = 8 - Total = 17

and if that was actually a different weekday
TOTAL HOURS for payment of overtime = 46.5 payment at standard hourly rate

have I got that right

11. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
so I have modified the formula in columns S - W to work out the 1.5 and the 2 times hours - so thats the equivalent Hours to pay
and then put that total into column O as potential hours to be paid

Now in column P - I check the hours actually worked in column B - F and if greater than 40 - I use the potential Hours for overtime payment

but sure this is not correct yet

i'm on UK time zone and out most of tomorrow - so may not get to this until weekend - but as I say it should be possible to create an automatic spreadsheet to work all this out

#### Attached Files:

• ###### For Tech Forum_etaf-1.xlsx
File size:
12.9 KB
Views:
46
12. Thank you for all your time spent on this so far!

I've noticed that the Total Calculated Overtime Hours isn't matching what I've entered into the green columns. The formulas should return exactly what I have in the total calculated overtime hours column. I'm not sure how you got 4.65 for example on line 6.

13. Sheet1 is based on your double counting description of Billy.
Sheet2 is based on what I understood from the whole thread (weekends and holidays are counted as OT for all hours worked.
Your whole calculations are flawed by not separating 1.5x OT and 2x OT.
Also there is no way that the >40hours rule will ever get triggered, based on your criteria.

File size:
15.9 KB
Views:
49
14. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
Garf13LD has posted a much better solution , using sumproduct,and is far better at excel then I - so you are in capable hands

i got 4.65 by looking at each day and anything over 8 hours I then used the @1.5X rate ,and anything over the 12 hours I use the @2X rate - which is different, because of the 7.5 hours worked on other days, which I have not factored in, I just used was more than 40 hours worked in the week

I think Garf13LD has raised a few further questions, so for the moment I will leave with Garf13LD so not to confuse here

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1084588