Results 1 to 3 of 3
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Format time > 24 hours (any ... ?)

    I'm having trouble with something I assumed would be easy. In Excel, time periods greater than 24 hours can be cell formatted via the format <!t>[h]:mm:ss. However, that isn't valid in VBA , and I can't seem to find (in this forum and a general google) anything simpler than the following hideous example:

    Sub formattimeover24hours()
    Const dblTime As Double = 3.337 ' in Excel <!t>[h]:mm:ss format, 80:05:17, i.e. 80 hours, 5 minutes, 17 seconds
    Dim strMinSec As String
    strMinSec = Format(((dblTime * 24) - Int(dblTime * 24)) / 24, "hh:mm:ss") '*
    strMinSec = ":" &Right(strMinSec, Len(strMinSec) - InStr(strMinSec, ":"))
    MsgBox "Time h:mm:ss = " & Int(dblTime * 24) & strMinSec
    End Sub

    *Mod also works differently in VBA than in Excel, in that in VBA it discards fractional parts of the return, or did I miss something there?

    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> Please oh please tell me there's a simple way.
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Format time > 24 hours (any ... ?)

    <P ID="edit" class=small>(Edited by jscher2000 on 21-Jan-04 17:24. Shortened it a bit.)</P>This is shorter, and I can understand it better, but you still have to "construct" the string.

    Sub formattimeover24hours()
    Const dblTime As Double = 3.337 ' in Excel [h]:mm:ss format, 80:05:17, i.e. 80 hours, 5 minutes, 17 seconds
    Dim strTimeLikeExcel As String
    strTimeLikeExcel = CStr(Int(dblTime) * 24 + Hour(dblTime - Int(dblTime))) & Format(dblTime, ":nn:ss")
    MsgBox strTimeLikeExcel
    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Format time > 24 hours (any ... ?)

    Thanks, the ":nn" got me on track! Even better:

    strTimeLikeExcel = Int(dblTime * 24) & Format(dblTime, ":nn:ss")
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •