Simple elapsed time expression giving #error

[Al77]

Hi folks!

I have two fields in a table for entering elapsed time in the format hh:nn:ss or nn:ss, depending on whether there are any hours (rare).

When I create a SIMPLE expression to convert the above entries into decimal MINUTES, only the hh:nn:ss version works, the other returning an #Error message.

This has completely thrown me because it seems so simple and I have not fiddled with any other settings (as far as I know).

Expression = Minute([TimeN(ms)]), which is virtually IDENTICAL to the other version = Minute([TimeN(hms)]) that actually works !

Alternatively, is there any way of creating a field that allows quick / clean entry of time in EITHER format? i.e. WITHOUT leading zeros when there are no hours, essentially pushing the first four entries to the last four places when there are no hours to be recorded (if that makes sense) - i.e. when 1250 is entered, it shows as 00:12:50.

Any ideas?



Thanks

Al

[June7]

Are these date/time type fields?

Need all 3 time parts for calculation.

One field to allow either input? Not without VBA.

Could input into 3 fields then in code concatenate for calculations.

Minute() function only pulls the minutes part, ignores the hours and seconds. Is that what you really want?

[moke123]

Function ElapsedTime(endTime As Date, startTime As Date)
    Dim strOutput As String
    Dim Interval As Date


    ' Calculate the time interval.
    Interval = endTime - startTime


    ' Format and print the time interval in seconds.
    strOutput = Int(CSng(Interval * 24 * 3600)) & " Seconds"
    Debug.Print strOutput


    ' Format and print the time interval in minutes and seconds.
    strOutput = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss") _
              & " Minutes:Seconds"
    Debug.Print strOutput


    ' Format and print the time interval in hours, minutes and seconds.
    strOutput = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss") _
              & " Hours:Minutes:Seconds"
    Debug.Print strOutput


    ' Format and print the time interval in days, hours, minutes and seconds.
    strOutput = Int(CSng(Interval)) & " days " & Format(Interval, "hh") _
              & " Hours " & Format(Interval, "nn") & " Minutes " & _
                Format(Interval, "ss") & " Seconds"
    Debug.Print strOutput


End Function

[Al77]

Basically, I created a two or three part expression to extract all minutes = 1. Hours*60 + Minutes + Seconds/60, or 2. just Minutes + Seconds/60.

Data entered as hh:nn:ss or nn:ss, as menioned.

Maybe i am going about this the wrong way around and you know a quicker way / expression?

[moke123]

this function should do what you want

Function ElapsedTime(endTime As Date, startTime As Date) As String
    Dim strOutput As String
    Dim Interval As Date


    Interval = endTime - startTime


    If Int(Format(Interval, "hh")) <> 0 Then
        strOutput = strOutput & (Format(Interval, "hh")) & ":"
    Else
        'strOutput = strOutput & "00:"  'uncomment if you want to include 00: value for no hours
    End If


    If Int(Format(Interval, "nn")) <> 0 Then
        strOutput = strOutput & (Format(Interval, "nn")) & ":"
    Else
        strOutput = strOutput & "00:"
    End If


    If Format(Interval, "ss") <> 0 Then
        strOutput = strOutput & Format(Interval, "ss")   
    Else
        strOutput = strOutput & "00"
    End If


    ElapsedTime2 = strOutput


End Function

results will look like

01:02:27
02:27

personally I think it may be confusing to have 2 different formats
If you un-comment the commented line, if theres no hours it will output

00:02:27