If Formula

  • Thread starter Thread starter natei6
  • Start date Start date
N

natei6

=IfSUM(D6:D12<40/24,SUM(D6:D12/24),IfSUM(D6:D12>40/24,("40:00:00")))

I'm working on a time sheet, and I keep getting an error on thi
formula, it asks for name. If the cell range is less than 40 Hours
want the sum of the cell range. If the sum of the cell range i
greater than 40 hours I want the result to be "40:00:00". Any hel
would be appreciated.
 
Hi
there is no function as IfSUM :-)
You also divided within the range of a SUM (e.g. SUM(D6:D12/24) ->
won't work)

Try the following
=MIN(SUM(D6:D12),40/24)
and format this resulting cell with the custom format (goto 'Cells -
Format' for this):
[hh]:mm
 
Thankyou, that helped a great deal, but that brings me two more
questions.

1. How do I amend the carry over hours to only be added to overtime if
the total is under 40 hours?

2. How do I get the Reg time for P.P. to only add if the total is under
40?

Do you understand what I'm trying to say?

Attachment filename: time card sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=477896
 
Hi
no i don't totally understand. You may post some example data (in plain
text - no attachments please) to descirbe this :-)
 
Let me try again
Carry over Hours 41:00:00

Week One, Monday
Week One, Tuesday
Week One, Wednesday 8:00
Week One, Thursday 8:00
Week One, Friday 16:00
Week One, Saturday 16:00
Week One, Sunday
Week One, Total 89:00:00 (formula) =C1+SUM(C4:C10)
Week One, Regular 40:00:00 (formula
=MIN(C1+SUM(C4:C10),40/24)
Week One, Reg This P.P. 40:00:00 (formula) =MIN(SUM(C4:C10),40/24)
Week One, Overtime 49:00:00 (formula
=IF(C11>40/24,C11-40/24,0)


Total Hours is right.
Week one regular is right
Week one regular this Pay period shoud be zero
Week one overtime should be only 48.
How do I amend the formulas
 
Hi,

41 hours are carry over from the last pay period. any thing over 4
would be paying him twice because the carryover is just to calculat
overtime. If carryover is over 40 then it miscalculates overtime
 
Hi
still not quite sure but try (if the carry over is in cell C2)
=IF(C11>40/24,C11-40/24-MAX(0,C2-40/24),0)
 
Dear Frank

Thank you sooo much, you are genius. One more question; how do I ge
the "Regular time this pay period" to calculate in proportion to th
carry over hours? In this case it should be zero because there wa
over forty hours for the week before the pay period began.




Thanks again,

Nathan Sargeant

Lebanon, Oregon, US
 
Does this help?

Carry over Hours 41:00:00
Week One, Monday
Week One, Tuesday
Week One, Wednesday 8:00
Week One, Thursday 8:00
Week One, Friday 16:00
Week One, Saturday 16:00
Week One, Sunday
Week One, Total 89:00:00 (formula) =C1+SUM(C4:C10)
Week One, Regular 40:00:00 (formula) =MIN(C1+SUM(C4:C10),40/24)
Week One, Reg This P.P. 40:00:00 (formula) =MIN(SUM(C4:C10),40/24)
Week One, Overtime 48:00:00 (formula
=IF(C11>40/24,C11-40/24-MAX(0,C1-40/24),0)


Total Hours is right.
Week one regular is right
Week one regular this Pay period shoud be zero because all the hour
are overtime in this pay period.
Week one overtime is correct
How do I amend the formula?


Thanks Again

Nathan Sargeant


*=MIN(SUM(C4:C10),40/24)
 
Hi
no :-)

I would need some example for your calculation of your proportion in
respect to the carry over time.
 
Hi, thanks for your patience.

If carryover hours are 20, in A1 and Payperiod hours are = to 30 i
A2:A8, how do I get A9 to = 10

Thanks,

Nathan Sargean
 
Hi Frank,
Let me try to better explain my problem.

1. Carry over Hours 30:00:00
2. Week One, Monday
3. Week One, Tuesday
4. Week One, Wednesday
5. Week One, Thursday
6. Week One, Friday 5:00:00
7. Week One, Saturday 10:00:00
8. Week One, Sunday 5:00:00
9. Week One, Total 50:00:00 Row 9. is Right Su
(B1:B9)
10.Week One, Regular 40:00:00 Row 10. is Right
MIN(SUM(B1:B8),40/24)
11.Week One, Reg This P.P. #VALUE! This should be 10:00:00
12.Week One, Overtime 10:00:00 Row 12. is righ
=IF(B9>40/24,B9-40/24-MAX(0,B1-40/24),0)

If B1 is 40 or greater B11 Should be 0. If B1 is less than 40, B1
should = Min(Sum (B2:B8),40/24)-B12
How do I Write the formula for B11?

Thanks Again
Nathan Sargean
 
Hi Frank
This works fine if B1 is greater than or equal to 40, but if less tha
40 the result is "#Name?". More suggestions?

Thanks,
Nathan Sargeant
 
Hi
formula looks o.k but try (without a space in the formula)
=IF(B!>=40/24,0,MIN(SUM(B2:B8),40/24)-B12)
 
Hi Frank,

Thankyou! =IF(B1>=40/24,0,MIN(SUM(B2:B8),40/24)-B12) Works great but I
realize I need to add an argument. Add if B1 is 0
MIN(SUM(B2:B8),40/24)
How do I put that all together?

All the Best,

Nathan Sargean
 
Back
Top