Calculating a time sheet?
 

I currently have a time sheet set up with columns for my employees hours for
the day then total for the week, then I have a column that adds their wage
for me and totals everything up.

Now the question I have is we are trying to break it up (instead of by hand)
anything over 44 hours is overtime, is there a formula I can use to have for
my regular hours in 1 column x the regular wage, then another column for O.T.
hours less regular hours x O.T. rate?

For example : B8:B14 (is 6 days of total hours worked) in Col. B15
But I want to keep the total in B15 at 44 hours or
less. Then the remaining hours I want to go to the next column for O.T. (B16)
which I will then use the formula to work out that wage.

Is this just asking for a lot from excel? or does anyone have any other
suggestion for keeping track of breaking the hours down.

Thanks
tb

Hi!

B15:

=MIN(44,SUM(B8:B14))

B16:

=MAX(0,SUM(B8:B14)-44)

Biff

"tb" wrote in message
...
I currently have a time sheet set up with columns for my employees hours
for
the day then total for the week, then I have a column that adds their wage
for me and totals everything up.

Now the question I have is we are trying to break it up (instead of by
hand)
anything over 44 hours is overtime, is there a formula I can use to have
for
my regular hours in 1 column x the regular wage, then another column for
O.T.
hours less regular hours x O.T. rate?

For example : B8:B14 (is 6 days of total hours worked) in Col. B15
But I want to keep the total in B15 at 44 hours or
less. Then the remaining hours I want to go to the next column for O.T.
(B16)
which I will then use the formula to work out that wage.

Is this just asking for a lot from excel? or does anyone have any other
suggestion for keeping track of breaking the hours down.

Thanks
tb

Biff
Won't that pay 44 hours even if 38 hours (for example) are worked?

perhaps
B15 =if(sum(B8:B14)44,44,sum(B8:B14))
and
B16 =if(sum(B8:B14)<=44,0,sum(B8:B14)-44))


--
Regards

Roger Govier


"Biff" wrote in message
...
Hi!

B15:

=MIN(44,SUM(B8:B14))

B16:

=MAX(0,SUM(B8:B14)-44)

Biff

"tb" wrote in message
...
I currently have a time sheet set up with columns for my employees hours
for
the day then total for the week, then I have a column that adds their
wage
for me and totals everything up.

Now the question I have is we are trying to break it up (instead of by
hand)
anything over 44 hours is overtime, is there a formula I can use to have
for
my regular hours in 1 column x the regular wage, then another column for
O.T.
hours less regular hours x O.T. rate?

For example : B8:B14 (is 6 days of total hours worked) in Col. B15
But I want to keep the total in B15 at 44 hours or
less. Then the remaining hours I want to go to the next column for O.T.
(B16)
which I will then use the formula to work out that wage.

Is this just asking for a lot from excel? or does anyone have any other
suggestion for keeping track of breaking the hours down.

Thanks
tb

Hi!

Won't that pay 44 hours even if 38 hours (for example) are worked?

No

=MIN(44,SUM(B8:B14))

If the sum of hours in B8:B14 = 38

=MIN(44,38)

If the sum of hours in B8:B14 = 56

=MIN(44,56)

Biff

"Roger Govier" wrote in message
...
Biff
Won't that pay 44 hours even if 38 hours (for example) are worked?

perhaps
B15 =if(sum(B8:B14)44,44,sum(B8:B14))
and
B16 =if(sum(B8:B14)<=44,0,sum(B8:B14)-44))


--
Regards

Roger Govier


"Biff" wrote in message
...
Hi!

B15:

=MIN(44,SUM(B8:B14))

B16:

=MAX(0,SUM(B8:B14)-44)

Biff

"tb" wrote in message
...
I currently have a time sheet set up with columns for my employees hours
for
the day then total for the week, then I have a column that adds their
wage
for me and totals everything up.

Now the question I have is we are trying to break it up (instead of by
hand)
anything over 44 hours is overtime, is there a formula I can use to have
for
my regular hours in 1 column x the regular wage, then another column for
O.T.
hours less regular hours x O.T. rate?

For example : B8:B14 (is 6 days of total hours worked) in Col. B15
But I want to keep the total in B15 at 44 hours or
less. Then the remaining hours I want to go to the next column for O.T.
(B16)
which I will then use the formula to work out that wage.

Is this just asking for a lot from excel? or does anyone have any other
suggestion for keeping track of breaking the hours down.

Thanks
tb

Hi Biff
Apologies. I was not reading that straight. My eyes saw MIN, my brain though
MAX. Too much wine with dinner last night!!!

--
Regards

Roger Govier


"Biff" wrote in message
...
Hi!

Won't that pay 44 hours even if 38 hours (for example) are worked?

No

=MIN(44,SUM(B8:B14))

If the sum of hours in B8:B14 = 38

=MIN(44,38)

If the sum of hours in B8:B14 = 56

=MIN(44,56)

Biff

"Roger Govier" wrote in message
...
Biff
Won't that pay 44 hours even if 38 hours (for example) are worked?

perhaps
B15 =if(sum(B8:B14)44,44,sum(B8:B14))
and
B16 =if(sum(B8:B14)<=44,0,sum(B8:B14)-44))


--
Regards

Roger Govier


"Biff" wrote in message
...
Hi!

B15:

=MIN(44,SUM(B8:B14))

B16:

=MAX(0,SUM(B8:B14)-44)

Biff

"tb" wrote in message
...
I currently have a time sheet set up with columns for my employees hours
for
the day then total for the week, then I have a column that adds their
wage
for me and totals everything up.

Now the question I have is we are trying to break it up (instead of by
hand)
anything over 44 hours is overtime, is there a formula I can use to
have for
my regular hours in 1 column x the regular wage, then another column
for O.T.
hours less regular hours x O.T. rate?

For example : B8:B14 (is 6 days of total hours worked) in Col. B15
But I want to keep the total in B15 at 44 hours or
less. Then the remaining hours I want to go to the next column for O.T.
(B16)
which I will then use the formula to work out that wage.

Is this just asking for a lot from excel? or does anyone have any other
suggestion for keeping track of breaking the hours down.

Thanks
tb