 # Calculating overtime in Excel

• Hi,
I`m trying to calculate overtime when I have more than 40 hours per week.
I have an spreadsheet where I enter the data like start time, end time and brake as result I have for example 57:00 Hours per week and need to calculate 40 hours per regular rate and the hours over 40 by 1.5.
So far tried
A1 = 57:00 - total hours
In cell B1 I`m using IF function: IF A1>40; total hours-40;else =0 this way i will get overtime hours or 0 if there is no overtime =17 in current example. Then in next cell C1 =A1-B1 = 40 from here I can multiple the overtime hours and regular hours the way I want.
The problem here is A1 has value 57:00 and I don`t know how to proper enter the if function =IF(A1>40;A1-40;0) where 40 is regular hours. A1 is 57:00 I've tried with 40 and 40:00 but can't get any result.
How to enter this 40 to be like hours?

Thanks for your time and help!

## Files

• Re: Calculating overtime in Excel

• Re: Calculating overtime in Excel

Thanks!

• Re: Calculating overtime in Excel

You need to change the Cell formatting for G4 and G5 to general and then enter the below formulas;

G4=IF(TEXT(E11,"[hh]")>=40,40,TEXT(E11,"[hh]"))
G5=IF(TEXT(E11,"[hh]")>40,TEXT(E11,"[hh]")-40,0)

Dan

[SIZE=1]It's like asking a mechanic to fix your car, without actually taking your car to him. Post your code/file and you'll get much quicker and more accurate solutions to your problem.[/SIZE]

• Re: Calculating overtime in Excel

Dan,
Thank you very much!

• Re: Calculating overtime in Excel

You're welcome.

[SIZE=1]It's like asking a mechanic to fix your car, without actually taking your car to him. Post your code/file and you'll get much quicker and more accurate solutions to your problem.[/SIZE]

• Re: Calculating overtime in Excel

Hi Dan,
Just found a problem in the sheet.
When I have 40 h 05 min it doesn't calculate them right.
Also if I have less than 40 for example 39 hours it's like 39h x \$1 then -1h overtime = \$39 - \$1.5 overtime h.
Can yo help me with this please?
Thanks!

• Re: Calculating overtime in Excel

I had to change the layout a little, but this works well.

forum.ozgrid.com/index.php?attachment/55668/

Dan

## Files

[SIZE=1]It's like asking a mechanic to fix your car, without actually taking your car to him. Post your code/file and you'll get much quicker and more accurate solutions to your problem.[/SIZE]

• Re: Calculating overtime in Excel

Hi Dan,
Sorry for my late reply and thanks for trying to help me.
Seems like if someone worked just 2 day 10 hour per day he still get 4 hours overtime even if the person worked less than 40 hours.
Also if the person worked 5 days in the week 8 hours per day and for example he worked one more day for 6 hour this 6 hours are calculated regular hours not overtime hours.
I still believe it should be easiest way to calculate only overtime hours worked over 40 hours.

• Re: Calculating overtime in Excel

Quote from mike2000;681409

Hi Dan,
Sorry for my late reply and thanks for trying to help me.
Seems like if someone worked just 2 day 10 hour per day he still get 4 hours overtime even if the person worked less than 40 hours.
Also if the person worked 5 days in the week 8 hours per day and for example he worked one more day for 6 hour this 6 hours are calculated regular hours not overtime hours.
I still believe it should be easiest way to calculate only overtime hours worked over 40 hours.

It may be different where you come from, but here in Australia, if you work more than 8hrs in a single day, the "overtime" hrs are calculated at an overtime rate. Even if you work less than 40 hrs per week (unless you are paid a casual wage which overtime hrs are not always applied to).

In the case that you have worked a 6th day in a week (ie. a day on the weekend), all hours on that day would be considered overtime.

How do you want it calculated? The attachment treats Saturday and Sunday as entirely overtime.

forum.ozgrid.com/index.php?attachment/55991/

## Files

[SIZE=1]It's like asking a mechanic to fix your car, without actually taking your car to him. Post your code/file and you'll get much quicker and more accurate solutions to your problem.[/SIZE]

• Re: Calculating overtime in Excel

Hi,