Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 40
Default overtime hours formula

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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 40
Default overtime hours formula

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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,345
Default overtime hours formula

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








  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 40
Default overtime hours formula

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











  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default overtime hours formula

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.
--
David Biddulph

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











  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 40
Default overtime hours formula

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!!
--
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









  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default overtime hours formula

On Dec 16, 10:11 am, "Sandy Mann" wrote:
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).
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,345
Default overtime hours formula

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


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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
if then formula for total time ;overtime hours versus regular OTVs Regular Excel Worksheet Functions 1 September 13th 07 08:24 AM
regular and overtime hours Curtis Excel Worksheet Functions 1 April 21st 07 06:32 AM
Overtime Hours Zack Excel Worksheet Functions 5 November 23rd 05 05:28 PM
Calculating Overtime from Hours total Dreamweavn via OfficeKB.com Excel Worksheet Functions 6 April 29th 05 11:21 PM
need help w/formula for calculating overtime hours jv749297 Excel Worksheet Functions 1 January 17th 05 08:54 PM


All times are GMT +1. The time now is 02:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"