Excel: Complicated Overtime Formula Help

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Sarahml

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! etaf

Wayne
Moderator
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

9 hours is greater than (47-40) so I would use 9 hours
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

Attachments

• 10.7 KB Views: 62

Sarahml

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.

Sarahml

Sorry forgot the attachment!

Attachments

• 11.2 KB Views: 41

etaf

Wayne
Moderator
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
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.
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

Attachments

• 13.1 KB Views: 33

etaf

Wayne
Moderator
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

Sarahml

Please see my notes in red, and thanks again! 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 - Yes

if the result was 6
which is not greater than 7 - what would you use - 7, I would always use whichever number was greater

any reason for the 47 , would that 7 apply to all calculations - the 7 would only apply to all calculations if it was greater than the overtime calculation (this often happens if someone works on a weekend)
just need those scenarios to be able to formulate

etaf

Wayne
Moderator
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

Sarahml

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...
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

etaf

Wayne
Moderator
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

If a person works more than 8 hours a day, anytime afterwards is considered overtime and is paid at time and a half
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

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.
so the hours actually need to change to reflect that assume 14hrs
4@*1.5 and 2 @ *2 = so the hours should show 6 + 4 = 10hours

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)
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

etaf

Wayne
Moderator
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

Attachments

• 12.9 KB Views: 46

Sarahml

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.

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

Garf13LD

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.

Attachments

• 15.9 KB Views: 49

etaf

Wayne
Moderator
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

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.