How do I Convert String Time Value to Decimal Value

[JeffGrant]

Hi All, I am converting a large number of records from an Excel Power Query csv import (from many files) to Access so that I can take advantage of incremental daily updates. Excel PQ does not like this. However, what is happening is this..



1. Access imports a time field as mm:ss.00 as a short text field. No problem.
2. The imported text string looks like this: 1:12.45 which is 1 minute, 12.45 seconds as text. No Problem.
3. What I would like to end up with 72.45 seconds as a decimal number.

How do I do this conversion. This is really easy in Excel and PQ but with Access, I have no idea as I am learning a new craft.

All suggestions are graciously received.

Regards

Jeff

[Micron]

I think you'll need a somewhat complex custom function. While there are built in functions such as Minute and Hour, your value looks like Time, but not quite. In fact, it's duration, not time. Hour(1:12.45) returns 12 because Access thinks that's 12 minutes there. It's getting late here and I'm not paying enough attention to game 1 (World Series) so I don't think I can come up with something tonight but we'll see. Perhaps someone will weigh in with a solution in the meantime.

EDIT - you provided just one time example but that's not enough to go on. All values are one digit, semicolon, 2 digits, period, 2 digits? All values will be strings (text)?
Also, you want it to just look like a decimal number but the data type is to remain text, or you actually want it to be a decimal data type? They are not the same thing even though they look the same.

[ArviLaanemets]

What is best way depends on what you want to do with those time values further. In case you want to add them to some another date/time/datetime value, or to summmarize them, then the preferable way is to save them as datetime values (i.e. your example will be like 1900.01.00 0:01:12,35 on fly), or as decimal numbers (i.e. your example will be 0,083854166...), or save every part of them (hours, minutes, and seconds) as separate fields . In case you want to display them only, save them as text.

About converting to decimal: convert the datestring to datetime value, and then convert the result to decimal. Or simply in Excel, add a column which reads your time values, format this column as General or Number, and in your query, read this column instead of time column.

About preferring datetime versus time in Access - when you run a query to summarize such times, the result field will inherit the format of source field. Unless I'm mistaken, when the source is Time, then all over 23:59:59,999... will be dropped. With datetime, it will be stored as days. Anyway I remember I had some problems with Time format in past, and I have avoided it since.

[davegri]

Here's a function that I came up with. As this immediate window shows, it will handle various mangled formats:

?fcntime("1:12.45")
72.45
?fcntime("10:0.5")
600.5
?fcntime("1:.1")
60.1
?fcntime("0:0.4")
0.4
?fcntime("1:")
60
?fcntime(":.33")
0.33

Public Function fcnTime(arg As String) As Double
    'arg expected as mm:ss.ff
    Dim warg As String
    Dim L As Integer
    Dim periodpos As Integer
    Dim mm As Integer   'minutes
    Dim ss As Integer   'seconds
    Dim ff As Integer   'fractional second
    arg = Trim(arg)
    warg = Replace(arg, Val(arg) & ":", "")
    warg = Replace(warg, ":", "")       'in case mm is absent
    periodpos = InStr(warg, ".")
    L = Len(warg)
    'Debug.Print "warg " & warg
    'fractions of seconds
    Select Case Len(warg) - periodpos
        Case 1
            ff = Right(warg, 1) & "0"
            warg = warg & "0"
        Case 2
            ff = Right(warg, 2)
        Case Else
    End Select
    L = Len(warg)
    'seconds
    Select Case L - periodpos
        Case 1
            ss = Left(warg, 1)
        Case 2
            ss = Left(warg, 2)
        Case Else
    End Select
    mm = Val(arg)   'minutes
    fcnTime = CDbl((mm * 60) + ss & "." & ff)
End Function

[JeffGrant]

Hi Davegi,

I have to show my complete embarrassment here. For the last several hours, every function i have tried from the internet, even yours, throws up a very basic error.

When I put the function call into the query, so that the query field name looks like this Expr1: fcnTime([form time]), when I view the query, I get the Error message "Undefined function 'whatever function name' in expression"

I have even copied some tutorials LETTER VERBATUM from youtube. The code in the video works, but i get this error message.

The function is placed in a standard VBA module.

What am i missing?

[CJ_London]

this works - providing a) the data is not mangled and b) the value will never be more that 24 hours (1440 minutes)

?(cdate(left("1:12.45",instr("1:12.45",".")-1))*24*60)+mid("1:12.45",instr("1:12.45","."))
72.45

and just to confirm it is numeric

?isnumeric((cdate(left("1:12.45",instr("1:12.45"," .")-1))*24*60)+mid("1:12.45",instr("1:12.45",".")))
True

in your query

Expr1 : cdate(left([Form Time],instr([Form Time],".")-1))*24*60)+mid([Form Time],instr([Form Time],"."))

[JeffGrant]

Hi Ajax,

that is perfect. thanks for the help.

[Micron]

What am I missing?

I don't suppose its scope is Private and not Public?
Anyway I coded for this during the game last night, based on the info I had. Maybe it will help someone else down the road.

Public Function getSeconds(strDuration As String) As Single
Dim x As Integer, y As Integer, intSecs As Integer

x = InStr(strDuration, ":") - 1
intSecs = Left(strDuration, x) * 60
y = InStr(x + 1, strDuration, ".") - 1
intSecs = intSecs + Mid(strDuration, x + 2, y - 2)
getSeconds = CSng(intSecs) + 0.45
MsgBox getSeconds

End Function