Format Time

[stottle]

Hello,

I have totalled up a column of cells which contain a time and when i
highlight all the cells and use the sum option in the bottom right of
excel (it gives you the option of sum, max, min, ave etc if you right
click there) it shows the value 652:47:46 (652 hours, 47 minutes and
46 seconds).

But when i use the format function in VBA, I get 04:47:48 (RangeTotals
is Variant which contains the number I would like to display)

ActiveCell.Offset(0, 6).Value = Format(RangeTotals, "Long Time")

When the time is formatted as a decimal i get 27.1998611111111.

Can someone help me so that the activecell value = 652:47:46 instead
of 04:47:48.

Many Thanks

Stuart

 

[Joel]

mytime = 27.1998611111111
myhours = Hour(mytime)
myminutes = Minute(mytime)
myseconds = Second(mytime)
mydays = Int(mytime)
myhours = 24 * mydays + myhours

ftime = myhours & ":" & Format(myminutes, "#0") & ":" & Format(myseconds,
"#0")

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim RangeTotals As Double
RangeTotals = 27.1998611111111 'my test data!
With ActiveCell.Offset(0, 6)
.NumberFormat = "[hh]:mm:ss"
.Value = RangeTotals
End With
End Sub