I am creating a spreadsheet in an early version of excel (02-03), for a
company who has several different employees. This company calculates
overtime on a daily basis, not weekly. I want Excel to automatically place
any hours beyond 8 into a separate column, so that those hours can be
calculated at overtime pay (time and a half). I am creating the master
sheet, so it has to be able to work even if there is no overtime for any
specific day. I can't just subtract 8 from every day, because then the
overtime column will go into the negative, and that cannot happen.
Therefore, I have to find a way for overtime hours to automatically be
filtered out into a separate column. I know all of the equations in order to
make the proper multiplication, but I just don't know how to filter out
overtime. Thanks so much!

Assuming your cell with the daily hours is in A1, put this in B1 (for your
overtime hours):
=If(A18,A1-8,0)

Then put this in C1 (for your regular, non-overtime hours):
=If(A1<=8,A1,8)

(I worked for a temp agency for 8 years)
- Cindy

"Hoov" wrote:

I am creating a spreadsheet in an early version of excel (02-03), for a
company who has several different employees. This company calculates
overtime on a daily basis, not weekly. I want Excel to automatically place
any hours beyond 8 into a separate column, so that those hours can be
calculated at overtime pay (time and a half). I am creating the master
sheet, so it has to be able to work even if there is no overtime for any
specific day. I can't just subtract 8 from every day, because then the
overtime column will go into the negative, and that cannot happen.
Therefore, I have to find a way for overtime hours to automatically be
filtered out into a separate column. I know all of the equations in order to
make the proper multiplication, but I just don't know how to filter out
overtime. Thanks so much!

This solution will solve your regular day overtime but overtime gets more
complicated when it comes to holidays. You won't be able to calculate
overtime this way, unless there is a specific code that will tell you if the
hours are regular hours or holiday hours. I have made a similar overtime
calculation and it was more complicated due to union agreement and holiday
pays.

CindyHoskey wrote:
Assuming your cell with the daily hours is in A1, put this in B1 (for your
overtime hours):
=If(A18,A1-8,0)

Then put this in C1 (for your regular, non-overtime hours):
=If(A1<=8,A1,8)

(I worked for a temp agency for 8 years)
- Cindy

I am creating a spreadsheet in an early version of excel (02-03), for a
company who has several different employees. This company calculates
[quoted text clipped - 8 lines]
make the proper multiplication, but I just don't know how to filter out
overtime. Thanks so much!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200905/1

Try this:

Over time:
=MAX(A1-8,0)

Regular time:
=MIN(A1,8)


"Hoov" wrote:

I am creating a spreadsheet in an early version of excel (02-03), for a
company who has several different employees. This company calculates
overtime on a daily basis, not weekly. I want Excel to automatically place
any hours beyond 8 into a separate column, so that those hours can be
calculated at overtime pay (time and a half). I am creating the master
sheet, so it has to be able to work even if there is no overtime for any
specific day. I can't just subtract 8 from every day, because then the
overtime column will go into the negative, and that cannot happen.
Therefore, I have to find a way for overtime hours to automatically be
filtered out into a separate column. I know all of the equations in order to
make the proper multiplication, but I just don't know how to filter out
overtime. Thanks so much!