if then formula for total time ;overtime hours versus regular

[Guest]

Hi I am a business owner and do my own invoicing /payroll I have a spread
sheet setup in excell for this.Currently my employees have there own column
where each day I insert there total hours worked so for example if you were
to sum up column l45:l51 this is the total hours the individual works in a
seven day period. I am trying to create underneath the sum at cell l52 and
l53 a formula that will seperate the regular hours l52 and overtime hours
l53. Overtime is based on hours worked over 40 in a week or if you start mid
week and total is 40hr or less then anything past 8 daily is overtime. So far
all I get to work is the sum I know not much any help anyone can give me
would be great....thnx

 

[Guest]

Hi,

It's a complex issue to give an exact answer because it depends on how you
have set up the worksheet. However, I'll give you some pointers on how you
might go about calculating your results.

Firstly assume 8hrs per day over 5 days totals 40hrs. Therefore overtime hrs
can be calculated as total hours less standard hrs. or as a formula:-

= Total overtime hrs - (8x5). Assuming a 5 day week

Now you can count the number of days that an employee worked by counting the
daily hours worked which exceed zero. Therefore you can calculate the normal
hours by multiplying this result by 8. the formula to do this is:-

=COUNT(A3:E3,">0") assuming that A3:E3 contains the daily total hours.

Therefore everyones overtime can be calculated by the following irrespective
of how may days they worked for the week:-

Overtime = Total hours worked - (COUNT(A3:E3,">0")) X 8

Hope it helps to put you on the right track.

Regards,

OssieMac