Day/Hour/Minutes Query

[pjheywood]

Can anyone tell me if there is a way of displaying a number, which represents the number of minutes of elapsed time, into the format days:hours:minutes elsapsed?

For instance the number (in minutes) 1530 would display as = 01:01:30. 1 day, 1 hour & 30 minutes of elapsed time.

Any help would be appreciated.

Paul.

 

[raskew]

Hi -

The following may seem like overkill, but this function will take a number which may represent days, hours, minutes or seconds and return an output in the user-defined format. To implement, copy function to a new module, then follow the comments to call.

HTH - Bob

Function TimeConvert(ByVal pTimecount As Double, _
                       Optional ByVal pTimeType As String = "s", _
                       Optional ByVal pDisplay As String = "dhns", _
                       Optional ByVal pBooZero As Boolean = True) _
                       As String
'*******************************************
'Purpose:   Convert number of days, hours,
'           minutes or seconds (default)
'           to a string showing any combina-
'           tion of days, hours, minutes,
'           seconds
'Coded by:  raskew
'Arguments: pTimeCount - count of time elements
'           (Opt) pTimeType - defaults to "s"
'           (Opt) pDisplay - defaults to "dhns"
'           (opt) pBooZero - show zero elements
'                 defaults to True
'Inputs:    1) ? TimeConvert(200000)
'           2) ? TimeConvert(200400, "s", "hns", False)
'           3) ? TimeConvert(200400, "s", "hns")

'Output:    1) 2 days 7 hours 33 minutes 20 seconds
'           2) 55 hours 40 minutes
'           3) 55 hours 40 minutes 0 seconds

'*******************************************

Dim TimeHold     As Double
Dim i            As Integer
Dim n            As Integer
Dim intAmt       As Double
Dim intervalHold As String
Dim strHold      As String
Dim strSay       As String
Dim strType      As String

    intervalHold = "dhns"
    TimeHold = pTimecount
    
    'clarify options
    pBooZero = IIf(IsMissing(pBooZero), True, pBooZero)
    pTimeType = IIf(IsMissing(pTimeType), "s", pTimeType)
    
    'correct common input error (representing minutes as "m", rather than "n")
    pTimeType = IIf(pTimeType = "m", "n", pTimeType)
    
    TimeHold = TimeHold * Choose(InStr("snhd", pTimeType), 1, 60, 3600, 86400)
    pDisplay = IIf(IsMissing(pDisplay), "dhns", pDisplay)
    'correct common input error
    If InStr(pDisplay, "m") > 0 Then
       pDisplay = Left(pDisplay, InStr(pDisplay, "m") - 1) & "n" & Mid(pDisplay, (InStr(pDisplay, "m") + 1))
    End If
    
    'verify display sequence
    strHold = ""
    For n = 1 To 4
       If InStr(pDisplay, Mid(intervalHold, n, 1)) > 0 Then
          strHold = strHold & Mid(intervalHold, n, 1)
       End If
    Next n
    pDisplay = strHold
    
    ' ...let it rip!
    For i = 1 To Len(pDisplay)
        strType = Mid(pDisplay, i, 1)
        intAmt = Int(TimeHold / Choose(InStr("dhns", strType), 86400, 3600, 60, 1))
        If intAmt <> 0 Or (intAmt = 0 And pBooZero) Then
           strSay = strSay & intAmt
           strSay = strSay & " " & Choose(InStr("dhns", strType), "day", "hour", "minute", "second")
           strSay = strSay & IIf(intAmt <> 1, "s ", " ")
        End If
        TimeHold = TimeHold - (intAmt * Choose(InStr("dhns", strType), 86400, 3600, 60, 1))
    Next i
    
    TimeConvert = strSay

End Function

 

[EMP]

Here is an alternative method, which would display 1530 minutes as 01:01:30

Format([Minutes]\(60*24),"00") & ":" & Format(([Minutes]\60) Mod 24,"00") & ":" & Format([Minutes] Mod 60,"00")

~