Solved

Turn total minutes to HH:MM

Posted on 2008-01-31
Medium Priority
396 Views
I have a variable that calculates total minutes when I display this I need the number to be formatted as HH:MM instead of only minutes
0
Question by:huerita37

LVL 86

Expert Comment

ID: 20794362
You could do something like:
``````Option Explicit

Private Sub CommandButton1_Click()
Dim totalMinutes As Integer
Dim formatted As String

totalMinutes = 93
formatted = FormatMinutes(totalMinutes)
MsgBox formatted
End Sub

Private Function FormatMinutes(ByVal totalMinutes As Integer) As String
FormatMinutes = Format(DateAdd("n", totalMinutes, Date), "Short Time")
End Function
``````
0

Author Comment

ID: 20794498
I tried using this but I am not getting the correct HH:MM.  For example,

my totalMinutes = 197236 and the function is returning 23:16  Obviously this is incorrect.  I did change the Interger type to Long because it was failing.  Could this be the problem?  Do I need to set it to something else?
0

LVL 4

Expert Comment

ID: 20794715
mod(hours,23) will give you days, (hours - mod(hours,23)*24) will give you the correct hours.
0

LVL 16

Accepted Solution

gnoon earned 2000 total points
ID: 20794773
>I did change the Interger type to Long because it was failing
Maximum value for Integer is 32767

However, is this you want

hhmm = min2HHMM(197236) '--> output: 3287:16
``````Function min2HHMM(ByVal minute As Long) As String
Dim h As Integer
Dim m As Integer
Dim hh As String
Dim mm As String
h = Fix(minute/60)
m = minute Mod 60
hh = CStr(h)
mm = CStr(m)
If h<10 Then hh="0" & h End If
If m<10 Then mm="0" & m End If
min2HHMM = hh & ":" & mm
End Function
``````
0

LVL 86

Expert Comment

ID: 20796944
Sorry....my approach will only work for values less than 24 hours.  For larger intervals you will need to calculate it manually as the others have shown...   =\
0

LVL 5

Expert Comment

ID: 20802085
Emulating Idle_Minds code...

I dont see why this would be retricted to 24 hours:
``````Function flr(byval thisValue as Double) As Double
If CLng(thisValue)>thisValue Then thisValue = thisValue - 1
thisValue = Clng(thisValue)
End Function

Function min2HHMM(ByVal minute As Double) As String
Dim h As Long
Dim hh As String, mm As String
h = flr(minute/60)
minute = minute - (h * 60) 'Wrap expression with CLNG for whole minutes - CLng(minute - (h * 60))

hh = CStr(h)
mm = CStr(minute)

While Instr(mm & ".", ".") < 2
mm = "0" & mm
Wend

While Len(hh)<2
hh = "0" & hh
Wend

min2HHMM = hh & ":" & mm
End Function
``````
0

LVL 5

Expert Comment

ID: 20802113
woops 2 mistakes there...

fixed below

``````Function flr(ByVal thisValue As Double) As Double
If CLng(thisValue) > thisValue Then thisValue = thisValue - 1
flr = CLng(thisValue)
End Function

Function min2HHMM(ByVal minute As Double) As String
Dim h As Long
Dim hh As String, mm As String

h = flr(minute / 60)
minute = CLng(minute - (h * 60))60))
' minute = minute - (h * 60))60) 'Use this version instead to include fractions of minutes

hh = CStr(h)
mm = CStr(minute)

While InStr(mm & ".", ".") < 3
mm = "0" & mm
Wend

While Len(hh) < 2
hh = "0" & hh
Wend

min2HHMM = hh & ":" & mm
End Function
``````
0

LVL 5

Expert Comment

ID: 20802128
omg, did it again

last one...
``````Function flr(ByVal thisValue As Double) As Double
If CLng(thisValue) > thisValue Then thisValue = thisValue - 1
flr = CLng(thisValue)
End Function

Function min2HHMM(ByVal minute As Double) As String
Dim h As Long
Dim hh As String, mm As String

h = flr(minute / 60)
minute = CLng(minute - (h * 60))

hh = CStr(h)
mm = CStr(minute)

While InStr(mm & ".", ".") < 3
mm = "0" & mm
Wend

While Len(hh) < 2
hh = "0" & hh
Wend

min2HHMM = hh & ":" & mm
End Function
``````
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.