Converting minutes to hours and minutes

[mondeoman]

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 it only gives the proportion of minutes in the hour.

 

[MazeWorX]

I use a function I wrote for this very thing see below.

Public Function ConvertTime(Time As Double)

    Dim vTime As Variant
    Dim mMin As String
    Dim cTime As String

    vTime = Split(Time, ".")
    If UBound(vTime) = 0 Then
        cTime = Time
    Else
        mMin = "." & vTime(1)
        mMin = Round(mMin * 60)
        cTime = vTime(0) & ":" & mMin
        'End If
    End If

    Debug.Print "Time = " & cTime
ConvertTime = cTime
End Function

ConvertTime(Time) accepts a double and can be called from a from a text box control source =ConvertTime([TotalMinutes])

You may want to add an IIf to handle nulls or the text box will show an error if no value has been passed to the function like
=IIf(IsNull([TotalMinutes]),"",ConvertTime([TotalMinutes]))

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TheAceMan1]

How are ya mondeoman . . .

... and this:

[blue}=Format(Int(TotalMinutes/60),"#0") & ":" & Format(Int((((TotalMinutes-Int(TotalMinutes))*60*10)+0.5)/10),"00")[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

mondeoman . . .

Sorry about the TGML confusion. Code should read:

[blue]   =Format(Int(TotalMinutes/60),"#0") & ":" & Format(Int((((TotalMinutes-Int(TotalMinutes))*60*10)+0.5)/10),"00")[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[mondeoman]

Thanks TheAceman1. This works but the problem is that it shows "00" minutes. I assume that this is because you have used Int. Is there a way round this so that it shows both the full hours and the minutes over. So for example at present the result is 16:00 where in reality it should be 16:20 (i.e. sixteen hours and 20 minutes)

 

[mondeoman]

Hi

I have now tried both the above and thanks for the input. As already mentioned TheAceMan1 solution uses an integer and therefore does not return the minutes only the hours. MazeWorX your solution simply gives me the total minutes and does not seem to convert it to Hours and minutes although the code would sem to suggest it should - so I am not sure what I am doing wrong.

 

[MazeWorX]

what is the data you are passing to the function. If you pass 1 it should return 1 if you pass 1.5 the result should be 1:30 or on re reading the original post are you passing total mins?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[mondeoman]

Yes I convert the elpased time i.e. start, finish (say 1:30 to 3:15) to minutes i.e. in the example 105 minutes. This become displayed in an invoice to show the customer exactly the total time for each job - in this case 1:45 where the 1 is an hour and the 45 are the remaining minutes. Incidentally I have put the function ConvertTime in a module and called it ConvertTime. This is called vis a text box called txttotaltime thus:
=IIf(IsNull([TotalMinutes]),"",ConvertTime([TotalMinutes]))
TotalMinutes being a text box (called TotalMinutes)where the source is on a query thus:
=Sum([qryInvoiceDetails]![TotalMinutes])
I am clearly doing something wrong - possibley the way I have called the function. Any advice would be helpful

 

[MazeWorX]

Nope ... not you but the function replace with this

Public Function ConvertTime(mTime As Double)

    Dim vTime As Variant
    Dim mMin As String
    Dim cTime As String
    Dim sTime As String
    
    sTime = mTime / 60
    vTime = Split(sTime, ".")
    
    If UBound(vTime) = 0 Then
        cTime = sTime & ":00"
    Else
        mMin = "." & vTime(1)
        mMin = Round(mMin * 60)
        cTime = vTime(0) & ":" & mMin

    End If
    
ConvertTime = cTime
End Function

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[mondeoman]

Yes but I now get an error as the report does not find the function ConvertTime. This function is called but somehow the report can't find it. It (The function) is in General declarations. And this is the code you have suggested:

Option Compare Database

Public Function ConvertTime(mTime As Double)

Dim vTime As Variant
Dim mMin As String
Dim cTime As String
Dim sTime As String

sTime = mTime / 60
vTime = Split(sTime, ".")

If UBound(vTime) = 0 Then
cTime = sTime & ":00"
Else
mMin = "." & vTime(1)
mMin = Round(mMin * 60)
cTime = vTime(0) & ":" & mMin

End If

ConvertTime = cTime
End Function

This is where it is called from the text box txtTotalTime control source:

=IIf(IsNull([TotalMinutes]),"",ConvertTime([TotalMinutes]))

All very strange but I am sure there is a solution - always is if you are clever enough to find it.

 

[MazeWorX]

place the function into a module the name of the module doesnt matter

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[dhookom]

the name of the module doesnt matter

The name of the module is critical since many newbies name the module the same as the function. This will result in an error something like the function can't be found.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

I have attached a link to the test db

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[TheAceMan1]

mondeoman . . .

I throughly tested the code I posted in A2k without a hitch! Even the time you posted 960.333 minutes (16Hrs 20min) works out right! I can't directly see why its not working for you ... and yes ... the [blue]Int[/blue] function is necessary to take care of the .5 accuracy correction. Using your latest example 960.333 as an example, I'll run thru the math:

[tt][blue]TotalMinutes = 960.333 minutes

The Hrs
-------
TotalMinutes / 60 = 960.333 / 60 = 16.00555
Int(16.00555) = 16hrs

Get The Mins
------------
TotalMinutes-Int(TotalMinutes) = 960.333 - 960 = 0.333
0.333 * 60 = 19.98min

Accuracy Correction
-------------------
19.98 * 10 = 199.8min
199.8 + 0.5 = 200.3min correction occurs here!
200.3 / 10 = 20.03min
Int(20.03) = 20min

Thats it. The two values are formated and concatenated proper for display:
16:20[/blue][/tt]

Since you say your stuck with two zero's, I'd look to the [blue]Format[/blue] string of the [blue]Format[/blue] function.

Again ... I'm having no problems at all.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

good point duane sometimes I assume too much

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[mondeoman]

Thanks guys. Much appreciated - now works fine. Basic problem was the function name so this is well worth passing on to others with a similar problem. Again thanks.