SUM range of time

[Lukael]

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 !!

[Greg M]

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:

"Total time - C column : " & Format(WorksheetFunction.Sum(Range("C1:C365")), "hh:mm") & " h"

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

Regards,

Greg M

[Lukael]

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 ?

[nilem]

Hi Lukael,
maybe so

Private Sub UserForm_Initialize()
Dim s$, dt#
s = "Average time - C column : " & Format(WorksheetFunction.Average(Range("C1:C365")), "N:ss") & " min" & vbNewLine & vbNewLine
dt = WorksheetFunction.Sum(Range("C1:C365"))
'dt * 24 = 60.8333 hours
'dtt = Int(dt * 24) & ":" & Round((dt * 24 - Int(dt * 24)) * 60, 0) = "60 h 50 m"
s = s & "Total time - C column : " & Int(dt * 24) & ":" & Round((dt * 24 - Int(dt * 24)) * 60, 0) & " h" & vbNewLine & vbNewLine
s = s & " Average time - H column : " & Format(WorksheetFunction.Average(Range("H1:H365")), "N:ss") & " min" & vbNewLine & vbNewLine
dt = WorksheetFunction.Sum(Range("H1:H365"))
s = s & " Total time - H column : " & Int(dt * 24) & ":" & Round((dt * 24 - Int(dt * 24)) * 60, 0) & " h"
TextBox1.Text = s
End Sub

[Lukael]

Norie,

looks like you had an A in mathematics when you were in school, It works

Can you comment these steps so that I can fully understand what you did ?


Thanks a lot, that's excellent programming !

[nilem]

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

[Lukael]

Bump, I'm sorry, I was in kind of rush when reading your post. My apologies nilem

Thanks for help !!