How to Sum<=40, sum, if >40 sum in next column

[justnotgettingit]

I am trying to sum a column of hours and minutes to calculate straight hours
and overtime. If the sum is <=40, then sum; otherwise move excess to the
next column. What is the foluma I should use to do this. I'm stumped.

 

[Luke M]

You will need to use two formulas.
First column formula:
=if(sum(a1:a5)<=40,sum(a1:a5),40)
Second column:
=if(sum(a1:a5)>40,sum(a1:a5)-40,"")

The second column will be blank if there is no overtime.

 

[ryguy7272]

try this:
=SUMPRODUCT(--(A2:A12>0),--(A2:A12<40),(A2:A12))

Regards,
Ryan--

 

[justnotgettingit]

Thanks Luke, that worked.

You will need to use two formulas.
First column formula:
=if(sum(a1:a5)<=40,sum(a1:a5),40)
Second column:
=if(sum(a1:a5)>40,sum(a1:a5)-40,"")

The second column will be blank if there is no overtime.

 

[Bernard Liengme]

With hours:Min in A1:A10
The total up to 40: =MIN(40/24,SUM(A1:A10)*24)
The excess over 40: =IF(SUM(A1:A10)*24>40,MOD(SUM(A1:A10),40/24),"")
Both cells formatted with [h]:m
best wishes

 

[Teethless mama]

Regular hours:
=MIN(SUM(A1:A5),40)

OT hours:
=MAX(SUM(A1:A5)-40,0)