why I cannot format SumAll as [h]:mm?



would "because it isn't an option with the format function" suffice.

Function SumAll(rng As Range)
Dim tot As Double
Dim cell As Range, dt As Double
SumAll = 0

For Each cell In rng
tot = tot + cell.Value
Next cell
dt = tot / 3600 ' converts second to hrs
hr = Int(dt)
Min = (dt - hr) * 60
SumAll = Format(hr, "0") & ":" _
& Format(Int(Min), "00")
End Function

If you don't want to truncate the seconds (which [h]:mm seems to do), then
remove the Int() part of the format argument.


--
Regards,
Tom Ogilvy


"JustMe" wrote in message
. ..
Could someone please tell me why I cannot format SumAll as [h]:mm? It
gives an answer of :03, when it should be 1618.20.

I'd also like to know how to format as Text "-[h]:mm" because I have some
other calculations that will result in a negative number of hours/minutes.

Thank you!

Function SumAll(rng As Range)
Dim cell As Range
SumAll = 0

For Each cell In rng
SumAll = SumAll + cell.Value
Next cell
SumAll = Format(SumAll / 86400, "[h]:mm") 'convert seconds to
hours/minutes
End Function