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

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 :o:o

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

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