If Formula

[natei6]

=IfSUM(D612<40/24,SUM(D612/24),IfSUM(D612>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.

 

[Frank Kabel]

Hi
there is no function as IfSUM
You also divided within the range of a SUM (e.g. SUM(D612/24) ->
won't work)

Try the following
=MIN(SUM(D612),40/24)
and format this resulting cell with the custom format (goto 'Cells -
Format' for this):
[hh]:mm

 

[natei6]

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

 

[Frank Kabel]

Hi
no i don't totally understand. You may post some example data (in plain
text - no attachments please) to descirbe this

 

[natei6]

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

 

[Frank Kabel]

Hi
why should it be 48 hours?
89 hours - 40 hours = 49 hours??
Did I miss something

 

[natei6]

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

 

[Frank Kabel]

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)

 

[natei6]

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

 

[Frank Kabel]

Hi
can you give some more examples - Did not understand this (it's getting
too late...)

 

[natei6]

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)

 

[Frank Kabel]

Hi
no

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

 

[natei6]

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

 

[Frank Kabel]

Hi
something like
IF(A1<40/24,SUM(A2:A8)-A1,0)

 

[natei6]

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

 

[Frank Kabel]

Hi
try
=IF(B1>=40/24,0,Min(Sum (B2:B8),40/24)-B12)

 

[natei6]

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

 

[Frank Kabel]

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)

 

[natei6]

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

 

[Frank Kabel]

Hi
try
=IF(B1>=40/24,0,MIN(SUM(B2:B8),40/24)-B12*(B1=0))