|
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 |
overtime hours formula
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 |
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 |
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 |
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 |
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 |
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). |
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 |
| All times are GMT +1. The time now is 03:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com