vba to covert text field to numeric

[JasChima]

I am recording and manipulating data on the time workers spend doing various tasks. All the data is to be stored and presented in the hours:minutes:seconds format.

In one case I need to store the total task time over a month, this value exceeds 24hrs so it can't be stored as a date/time datatype.

Instead I have recorded the values (eg. 137:25:56) as text. I need to parse the text to make it numeric, so it can be manipulated with the rest of the data.

I understand Access stores time values as a fraction of 1. i.e 1 hour = 1/24, 1 minute = 1/1440 and 1 second = 1/86400. I am a beginner with Access, so could someone offer code than can complete this task to read a text value (e.g. 137:25:56) and convert it to a numerical figure (e.g. 5.72634259).

Thanks in advance for your assistance.

 

[Jon K]

Try this function.

Public Function convDateTime(sTime As String)
   Dim DD As Long
   Dim HHNNSS As String
   
   DD = Left(sTime, InStr(sTime, ":") - 1) \ 24
   HHNNSS = Left(sTime,InStr(sTime,":")-1) Mod 24 & Mid(sTime,InStr(sTime,":"))
   
   convDateTime = DD + CDbl(CDate(HHNNSS))
   
End Function

.