Convert Mins:Secs To Hours:Mins Depend on units built

[Dreamweaver]

This is something I've been working on that very nearly works until it get to point DM

What I need to do is convert a build time for a single unit which is stored as mins:seconds into a build time depending on number of units being built then display the time as Hours:Mins.

I don't know of any functions in access that do this so have been playing with the code below which works upto the point of DM where I need to add the mins together then if more than 59 add the hour(s) to hours and then use the mins part as the total mins.

It needs a lot of cleanning up as been playing but hope somebody can help.

T = The Time IE 2:43
N = Number of units being built

Function GetTotalTime(T As String, N As Long) As String
Dim M As Double, S As Double, Y As Integer, MZ As Integer, MS As Long, H As Single, Min As Long, MM As Long
Dim DM As Long, Z As Integer
Y = InStr(T, ":")
If Y <> 0 Then
    'Do The Mins Convert To Hours First
    M = Left(T, Y - 1)
    If M > 0 Then
        M = M * N 'Minutes * Number Of Units
        'Convert Total Mins Into Hours
        M = M / 60
        Z = InStr(M, ".")
        H = Left(M, Z - 1)
        'The Left Over Mins
        Min = Mid$(M, Z + 1, Len(M))
    End If
    'Do The Seconds
    S = Mid(T, Y + 1, Len(T))
    If S > 0 Then
        S = S * N 'Seconds * Number Of Units
            If S > 59 Then 'Has Minutes
             S = S / 60 'Convert Total Seconds To Mins.Seconds
            MZ = InStr(S, ".")
            MM = Left(S, MZ - 1)
            'Dont worry bout odd seconds
            Else
            MZ = InStr(S, ".")
            MM = Left(S, MZ - 1)
            End If
    End If
End If
'Now Add The Mins Together then / 60 to get hour /mins and add the hours to hours the remaining mins are the total mins element for build.
DM = MM + Min

    GetTotalTime = H & ":" & DM
    
End Function

Many thanks

Mick

 

[Dreamweaver]

Well managed to solve the problems I was having in that it seems some values where being converted to strings so when I got to point Dm and tried adding both mins together it just combined them took me a while to notice the double quotes

I'm sure there's a simpler way to do this if so please let me know

This function does not round up the final mins and it needs a lot of cleaning up plus there may be the possibility of an error at "Z = InStr(S, ".")" should the number be a whole number.

Hope it helps somebody.

Function GetTotalTime(T As String, N As Long) As String
Dim M As Double, S As Double, Y As Integer, MZ As Integer, MS As Long, H As Single, Min As Long, MM As Long
Dim DM As Long, Z As Integer
Y = InStr(T, ":")
If Y <> 0 Then
    'Do The Mins Convert To Hours First
    M = Left(T, Y - 1)
    If M > 0 Then
        M = M * N 'Minutes * Number Of Units
        'Convert Total Mins Into Hours
        M = M / 60
        Z = InStr(M, ".")
        H = Left(M, Z - 1)
        'The Left Over Mins
        Min = CLng(Left(Mid$(M, Z + 1, Len(M)), 2)) + 1
    End If
    'Do The Seconds
    S = Mid(T, Y + 1, Len(T))
    If S > 0 Then
        S = S * N 'Seconds * Number Of Units
            If S > 59 Then 'Has Minutes
             S = S / 60 'Convert Total Seconds To Mins.Seconds
            MZ = InStr(S, ".")
            MM = CLng(Left(S, MZ - 1))
            'Dont worry bout odd seconds
            Else
            'MZ = InStr(S, ".")
            MM = S
            End If
    End If
End If
'Now Add The Mins Together then / 60 to get hour /mins
DM = MM + Min
    If DM > 59 Then
        S = DM / 60 'Get Hours
        Z = InStr(S, ".")
        MS = Left(S, Z - 1)
        H = H + MS
        'Now How many hours are there from the mins
        MS = MS * 60
        DM = DM - MS
    End If
    
    GetTotalTime = H & ":" & DM
    
End Function

 

[Dreamweaver]

This is a much better way of doing it thanks to Geoff from Kent Telephones.

It can also handle seconds.

Function GetTotalTime(T As String, N As Long) As String
Dim hourss As Long, mins As Long, secs As Long, timeinsecs As Long

mins = Val(Mid$(T, 1, InStr(T, ":")))
secs = Val(Mid$(T, InStr(T, ":") + 1))

timeinsecs = mins * 60 + secs
timeinsecs = timeinsecs * N
hourss = Int(timeinsecs / 3600)
mins = timeinsecs - (hourss * 3600)
mins = Int(mins / 60)
secs = timeinsecs - (hourss * 3600) - (mins * 60)
GetTotalTime = hourss & ":" & mins '& ":" & secs


End Function