Converting minutes to hours and minutes (1 Viewer)

I have a report that shows work undertaken on different jobs and the time taken for all jobs in minutes. I want to convert the total minutes to hour and minutes to show overall time elapsed. I can do it so it shows hours and proportion of hours i.e. 6.333 where the .333 is one third but how do I make it show 6:20 meaning sis hours twently minutes? At the moment I am using this:

=Sum([TotalMinutes])/60 & Format([TotalMinutes] Mod 60,"\:00")

but as said above it only gives the proportion of minutes in the hour.

try this - or fiddle wiith these functions to get what you want

Function timestrg(timeval As Double) As String
timestrg = Int(timeval) & " hours " & (timeval - Int(timeval)) * 60 & " minutes"
End Function

Sub doit()
MsgBox (timestrg(6.33))
End Sub

Thank you I will try your suggestion.

While I am sure that Dave's function will take what you are currently returning and convert it into what you are looking for, if you already have a "Total Minutes" and just want to return the "hh:mm" version then you can use the following function:

Function CalcHrsMins(TotalMinutes As Variant) As Variant
Dim varHours, varMinutes
'calculate the hours
varHours = Int(TotalMinutes / 60)
'calculate the remaining minutes
varMinutes = Format(TotalMinutes - (varHours * 60), "00")
'return the combined hours and minutes
CalcHrsMins = varHours & ":" & varMinutes
End Function

Click to expand...

Here is an example of how to call this funciton from a command button:

Private Sub Command1_Click()
Dim varTotHours As Variant
Dim varHrsMinutes As Variant
'assign the total hours to a variable
varTotHours = 135
'split the total minutes into Hours and Minutes
'by passing the total minutes to the function
varHrsMinutes = CalcHrsMins(varTotHours)
MsgBox varHrsMinutes
End Sub

Click to expand...