# Sum Time

#### EOAEvan

##### Active Member
I searched through many threads about time conversions / adding time etc and didnt seem to find anything that worked..

I have a report given to me formatted as general. These are overtime hours for 5000+ associates. The time is shown as 4.52 being 4 hours and 52 minutes.

If I sum 4.52, 5.1, .18... I get 9.8 when in fact it is 10hrs 20m. I need this to display as 10.2

I know this should be simple. In fact I have done it in the past but lets just say im ready for the weekend... Thank you in advance for your help!

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi

Assuming your values in A1:A3, try:

=--TEXT(SUMPRODUCT(0+SUBSTITUTE(TEXT(A1:A3,"0.00"),".",":")),"hh.mm")

I changed that to:

=--TEXT(SUMPRODUCT(0+SUBSTITUTE(TEXT(K9:K5400,"0.00"),".",":")),"hh.mm")

this is giving me 9.25

If I sum(k9:k54000)=1257.25.... I do not know the exact hours and minutes it should be as i did not count all 5392 rows manually... Do you have any ideas as to what would be messing this up?

If i change your formula to take a much smaller area with a different set of numbers than my orginal example it still seems to work but when changed to the full range it no longer works

Change the "hh.mm" in pgc's suggested formula to "[h].mm"

Hi

If the total is more than 24 hours, change the format. Try:

=--TEXT(SUMPRODUCT(0+SUBSTITUTE(TEXT(K9:K5400,"0.00"),".",":")),"[h].mm")

that did it.. thank you!

Next and what should be the last part... I need to incorporate that into a sumif() Here is what the standard sumif would be:

=SUMIF('[Account Lookup Table.xls]Paste Accounts from BTR Here'!\$J\$5:\$J\$6000,A11,'[Account Lookup Table.xls]Paste Accounts from BTR Here'!\$N\$5:\$N\$6000)

Thanks again

Bumpage...

Anyknow know how I can include this in a =sumif() ?

Thanks!

Try like this

=--TEXT(SUMPRODUCT(0+SUBSTITUTE(TEXT('[Account Lookup Table.xls]Paste Accounts from BTR Here'!\$N\$5:\$N\$6000,"0.00"),".",":"),--('[Account Lookup Table.xls]Paste Accounts from BTR Here'!\$J\$5:\$J\$6000=A11)),"[h].mm")

Thanks for the response Barry.. I am getting a #N/A using that formula when a manual check shows the correct answer should be 3.14... ideas?

Replies
1
Views
103
Replies
2
Views
352
Replies
7
Views
866
Replies
0
Views
261
Replies
1
Views
3K

1,196,514
Messages
6,015,649
Members
441,913
Latest member
Lhayden_69

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back