Sum Time

[EOAEvan]

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!

 

[pgc01]

Hi

Assuming your values in A1:A3, try:

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

 

[EOAEvan]

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

 

[barry houdini]

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

 

[pgc01]

Hi

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

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

 

[EOAEvan]

that did it.. thank you!

 

[EOAEvan]

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

 

[EOAEvan]

Bumpage...

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

Thanks!

 

[barry houdini]

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")

 

[EOAEvan]

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?