overtime hours formula

[Louie]

Hello, I need to generate a formula to add 5 weeks of work x hourly rate but
the total of 5 weeks is larger than 40 hours, how can I take away only the 40
hours.
here is the formula I have.
=SUM('week-1:Week-5'!I12)
this comes back with 48.25 hours and I need to get only 40 to multiply x
regular time.
and another one for the overtime.
thank you in advance for your help.

 

[Sandy Mann]

Try:

=MIN(40,SUM('week-1:Week-5'!I12))*Regular Rate

=MAX(0,SUM('week-1:Week-5'!I12)-40)*Overtime Rate

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Louie]

I am sorry...I am more confused that I can even imagine,
I am triying something easier, pardon my ignorance....
How can I do this..

=SUM(G12)-(K12) but only if the result is greater than 40 hours, if is less
or equal than 40 then write the result, in other words...I need to have 40
hours or less in that cell.
thank you for your help.

--
Louie

Try:

=MIN(40,SUM('week-1:Week-5'!I12))*Regular Rate

=MAX(0,SUM('week-1:Week-5'!I12)-40)*Overtime Rate

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

First of all you don't need the SUM(), SUM() adds together the cells or
ranges within the brackets eg SUM(A1:A10). SUM(G12 is the same as simply
writing G12 so your formula is actually: =G12-K12

To get 40 hours or less in that cell use:

=MIN(40, G12-K12)

If G12-K12 is greater than 40,(say 50 for example), then the MIN() function
will return the lesser of 50, 40 - in this case 40.

If G12 -K12 is less than 40, (so 30 for example), then the MIN() will return
the lesser of 40, 30 - in this case 30

To get the *remainder* ie the hours over 40 if any use:

=MAX(0,(G12-K12)-40)

If G12-K12 is 50 then (G12-K12-40) will evaluate to 10 so the MAX() of 0,
10 is 10
If G12-K12 is 30 then (G12-K12-40) will evaluate to -10 so the MAX() of
0,-10 is 0

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

I am sorry...I am more confused that I can even imagine,
I am triying something easier, pardon my ignorance....
How can I do this..

=SUM(G12)-(K12) but only if the result is greater than 40 hours, if is
less
or equal than 40 then write the result, in other words...I need to have
40
hours or less in that cell.
thank you for your help.

 

[Louie]

Hmmm...I pasted the formula =MIN(40, G12-K12)
on G12 I have 39.33 hrs and on K12 I have 0.00 the result of the formula
shows 40.00 not 39.33, any ideas??
I really appreciate your help.

 

[David Biddulph]

I suggest that you recheck your formula and your data.
If you are still getting a different result from what you're expecting, make
sure that under Tools/ Options/ Calculation you have the mode set to
Automatic, not Manual.

 

[Louie]

I am so sorry...I had a different formula on k12 that was affecting the
results. is all good now it does work perfect!! ...again...thank you so much
for your information it was greatly appreciated!!

 

[joeu2004]

SUM(G12 is the same as simply writing G12
so your formula is actually: =G12-K12

Except when G12 might contain "", for example due to an IF()
expression. Very sad that Excel does not handle that very common case
the same as an empty cell. But I would prefer to use N() to handle
such things instead of SUM(). Example: =n(G12)-n(K12). Also treats
any text as zero (e.g. " ", which some people write instead of "",
sadly).

 

[Sandy Mann]

Except when G12 might contain "", for example due to an IF()

However, surely in case there is an argument in favour of returning a
#VALUE! error rather than using N() which, in the second reference, will
return zero and thus produce a wrong result which may go unnoticed. If
there is going to be a problem I would rather know about it than have XL
hide the fact.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk