VBA formula for converting a sum of total minutes to actual hours/minutes

divide minutes by 1440

Assuming, you have numbers in A1, try this formula:

=TEXT((A1/1440),"hh:mm AM/PM")

Select allOpen in new window

If you want outcome in detailed Hours & Minutes then use below formula:

=HOUR(TEXT((A1/1440),"hh:mm AM/PM"))&" Hours "&MINUTE(TEXT((A1/1440),"hh:mm AM/PM"))&" Minutes"

Select allOpen in new window

You may try something like this...

Function MinutesToHoursAndMinutes(ByVal vMinutes As Long)
Dim strHours As String, strMinutes As String
If vMinutes >= 60 Then
    strHours = Int(vMinutes / 60) & " Hours"
    strMinutes = (vMinutes Mod 60) & " Minutes"
Else
    strHours = "0 Hour"
    strMinutes = vMinutes & " Minutes"
End If
MinutesToHoursAndMinutes = strHours & " " & strMinutes
End Function

Select allOpen in new window

To call this, you can pass minutes in the function like this...

Sub test()
MsgBox MinutesToHoursAndMinutes(400)
MsgBox MinutesToHoursAndMinutes(Range("A1").Value)
End Sub

Select allOpen in new window

Hi,

If you *need* it in VBA, then see above, but if you just want to convert a numeric value of 315 into 5:15, then if A1 = 315:

=A1/1440

Or

=A1/(60*24) - which is probably better as it is more explicit for someone trying to follow the logic

then format the cell with a custom format as required such as:

h:mm (shows as '5:15')

hh:mm (shows as '05:15')

h" hrs "mm" mins" (shows as '5 hrs 15 mins')

The other advantage of this is that the value of the cell (0.21875) is a real time value within Excel, so you can use it easily in other future calculations if required.

Alan.

Sorry folks, this is NOT Excell. It is VBA within an Acess DB.

Thanks
Ken

Thanks Ken for responding so quickly :)

Try below:

Format(<fieldname>, "hh:mm AMPM")

Select allOpen in new window

or

Int([fieldname]/60) & ":" & Format([fieldname] Mod 60,"00")

Select allOpen in new window

If we start by looking at, and understanding how time works in vba, and noticing that the fractional part of a date variable (which is really more like dateTIME, but hey) expresses the amount of time passed since midnight, and then considering we have 24*60 minutes in a day, we can quite easily convert any given amount of time into hours and minutes by using:

 format(NumberOfMinutes/(24*60),"hh:mm")
'e.g
 format(315/(24*60),"hh:mm")

Select allOpen in new window

which gives the result "05:15" (a string). This obviously doesn't take into account periods going above a full day.

If you want to store this as a date variable, i.e. convert it for storage , then use NumberOfMinutes/(24*60)

Hope that helps.

Hi,

Not sure about Access, but in Excel if you format the cell as [h]:mm then it will show the total in hours, even if more than 24 hours.

A1 = 1890
Format A1 as [h]:mm and it displays as 31:30

I would think that it is just as simple to work with hours in Access, but I haven't even opened Access since about 1999, so I could be wrong.

Alan.

Alan, no, in Access pure VBA, the hours start over at 24. Excel must be allowing it because it's a cell format.

Thanks

Noting again, this is in Excel VBA, the following works:

debug.Print WorksheetFunction.Text(1890/1440, "[h]:mm")

Gives:

31:30

However, I am guessing that WorksheetFunction.Text won't work in Access?

What about a calculated field (I assume they still exist) - could you do this in VBA:

TimeElapsedCalculated: [TimeElapsedInMins]\60 & Format([TimeElapsedInMins] Mod 60,"\:00")

Alan.

Access VBA gives. See how once it hit 24, the hours started over.. =(

?format(1890/1440, "hh:mm")
07:30

Okay, and I guess using the same format doesn't work either, nor the calculated field?

Access doesn't recog "[h]:mm" as a format

Darn!

Sorry - No more ideas.

Alan.

Figured it out myself