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.
--
Louie


"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Louie" wrote in message
...
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


"Sandy Mann" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"Louie" wrote in message
...
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.

--
Louie