SUM range of time

Hi,

I have two ranges of time where I need to calculate averages and SUM for both of them, displayed on Userform. One SUM works good, other not, everything else should be OK.

I guess something is wrong about calculating times over 24 hours period, and I can't fix It. Can somebody tell me what's wrong, and how to fix It ?

Please check sample and click on Userform, you'll see that SUM for column C is wrong

Any help appreciated !!

Hi there,

I think you've probably come up against a genuine Excel vs VBA BUG!

Cell C367 is correctly formatted to display ELAPSED time ("[hh]:mm"), so its value of 2.534... is displayed as 60:50. When formatted as hh:mm the value displayed is 12:50 - i.e. the same as appears on your UserForm.

BUT - it appears that this NumberFormat is not supported in VBA. I thought it would be simply a matter of changing the NumberFormat in your code to [hh]:mm, but that just doesn't seem to work:

Please Login or Register  to view this content.

I'd be quite happy to be proved wrong by any experts out there!

Regards,

Greg M

Hi Greg,

thanks for reply. I Can't believe, but your words are exactly my thoughts. I tried everything with format methods in VBA, but nothing worked, even [h]:mm:ss didn't do nothing.

I would be happy with any other solution, time format is not necessary, just output on Userform is desirable in "hh:mm". Maybe convert time to numbers and then text strings as a result on Userform ??

Or just put a cell text value on userfom and hide cell on sheet ?

I'm not Norie, don't worry (with all due respect to Norie)
and there are comments in the post #4