CODEWITHSUNDEEP
ms-accesstimevbams-access-2010
compass
compass

Reputation: 33

VBA - Access 2010 - Can't display total minutes in DD:HH:MM format if over 2 days

I need to convert total minutes to a DD:HH:MM format in VBA for Access 2010. HH:MM is straightforward, and I was able to resolve a total time over 24 hours like this:

If total / 1440 > 1 Then
    tbTimeTotal.Value = "1:" & Format(total / 60 / 24, "hh:nn")
Else
    tbTimeTotal.Value = Format(total / 60 / 24, "hh:nn")
End If

but the solutions I've tried for accommodating over 48 hours did not work:

First Try:

If total / 2880 > 2 Then
    tbTimeTotal.Value = "2:" & Format(total / 60 / 24, "hh:nn")
ElseIf total / 1440 > 1 And total / 2880 < 2 Then
    tbTimeTotal.Value = "1:" & Format(total / 60 / 24, "hh:nn")
Else
    tbTimeTotal.Value = Format(total / 60 / 24, "hh:nn")
End If

Second Try:

If total > 2880 Then
    tbTimeTotal.Value = "2:" & Format(total / 60 / 24, "hh:nn")
ElseIf total > 1440 And total < 2880 Then
    tbTimeTotal.Value = "1:" & Format(total / 60 / 24, "hh:nn")
Else
    tbTimeTotal.Value = Format(total / 60 / 24, "hh:nn")
End If

Suggestions? Is there a more elegant approach I can try?

Upvotes: 2

Views: 166

Answers (1)

HansUp
HansUp

Reputation: 97131

Use DateAdd to add your total minutes to day 0 which will give you a Date/Time value. You can then use date & time and other standard VBA functions with that value to create your custom time format.

Here are some examples based on the function below:

? CustomTimeFormat(720)
12:00
? CustomTimeFormat(790)
13:10
? CustomTimeFormat(1510)
1:01:10
? CustomTimeFormat(2955)
2:01:15

Notice you needn't code up all those math operations from your question because the VBA functions handle those details automagically.

Public Function CustomTimeFormat(ByVal pMinutes As Long) As String
    Dim lngDays As Long
    Dim strTime As String
    Dim strOutput As String
    Dim dteEquivalent As Date
    dteEquivalent = DateAdd("n", pMinutes, 0)
    lngDays = CLng(DateValue(dteEquivalent))
    strTime = Format(dteEquivalent, "hh:nn")
    strOutput = vbNullString
    If lngDays > 0 Then
        strOutput = lngDays & ":"
    End If
    strOutput = strOutput & strTime
    CustomTimeFormat = strOutput
End Function

Upvotes: 1

Related Questions