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!

 

[EOAEvan]

I may be able to use a helper column to make this work... If i use a sumif in one column then use =--TEXT(SUMPRODUCT(0+SUBSTITUTE(TEXT(B11,"0.00"),".",":")),"[h].mm") to convert it... It seems to work.

I would still prefer to have it all done in a single cell if possible but can use this method if needed.

 

[barry houdini]

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?

Are you using exactly the fomula I suggested? As far as I can tell it should work OK unless perhaps you have #N/As in column J. If so try changing to the following:

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

 

[EOAEvan]

That did it.. Thank you for your time Barry

 

[EOAEvan]

Ok, getting an error and not sure why.. This is on something else but using a formula provided earlier in this post...

Why does converting 144.84 using this =--TEXT(SUMPRODUCT(0+SUBSTITUTE(TEXT(C27,"0.00"),".",":")),"[h].mm") give me a #VALUE! error? It has worked on every other application so far.

 

[barry houdini]

144.84 isn't a vaild time value as the minute portion is greater than 59.....

 

[EOAEvan]

Though it works correctly on 18.86.. returning 19.26

im thinking it has to do with the hours.. as it only happens on the ones that are 100+ hrs

 

[barry houdini]

Now I've learnt something new, I didn't think it would recognise any of those but it seems Excel's quite happy to convert 18:86 to 19:26 but the cutoff appears to be 24 hours so 24.86 will cause an error.

You can use the following to accommodate those sorts of values....

For a single value you wouldn't need such a complex formula, e.g. to convert C27 you can use

=C27+(MOD(C27,1)>=0.6)*0.4

or for a range like A1:A10

=TEXT(SUMPRODUCT(SUBSTITUTE(TEXT(A1:A10+(MOD(A1:A10,1)>=0.6)*0.4,"0.00"),".",":")+0),"[h].mm")+0