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

  • Thread starter justnotgettingit
  • Start date
J

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.
 
L

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.
 
J

justnotgettingit

Thanks Luke, that worked.

Luke M said:
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.
Click to expand...
 
B

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
 
Top