Frustrated beyond belief with Time conversion

[CoachPhil]

I have been playing with trying to convert 'seconds' ( a six digit number) into hours;minutes;seconds.
I finally came up with the following code that will return the hh:mm:ss in the immediate window, but not in the application itself. It keeps sticking on 'MyHours = bigseconds \ 3600'
What am I missing?

Function converttime(bigseconds) As Variant
'Dim newtime As Variant
Dim MyHours As Variant
Dim myminutes As Variant
Dim myseconds As Variant
Dim bigseconds As Long
If bigseconds = "" Then
Debug.Print bigseconds
If IsError(bigseconds) = False Then
MyHours = bigseconds \ 3600
myminutes = (bigseconds - (MyHours * 3600)) \ 60
If Len(myminutes) < 2 Then myminutes = "0" & myminutes
myseconds = (bigseconds - (MyHours * 3600)) Mod 60
If Len(myseconds) < 2 Then myseconds = "0" & myseconds
Else
MyHours = 0
myminutes = 0
myseconds = 0
End If
If MyHours <> 0 Then
If Len(myminutes) < 2 Then myminutes = "0" & myminutes
converttime = MyHours & ":" & myminutes & ":" & myseconds
Else
converttime = myminutes & ":" & myseconds
End If
End Function

CoachPhil

 

[The_Doc_Man]

Too much work.

Convert the number of seconds to hh:mm:ss as follows:

1. Divide the number by 86400 - quotient is integer days. Remainder is fraction of a day.
...then...
2. Take CDbl( dayfrac ) and divide it by 86400.0 to make that a proper fraction.
...then...
3. Take DOUBLE fraction and use CDate() to convert it to date format.
...then...
4. Use the Format$ function and user-specified formatting or one of the time-only formats on that CDate conversion to give you hh:mm:ss

 

[MarkK]

Here's another spin on it...

Function ParseSeconds(ByVal seconds As Long) As String
   Dim period(3) As String
   Dim cons As Variant
   Dim i As Integer
   
   [COLOR="Green"]'construct an array of constants[/COLOR]
   cons = Array(86400, 3600, 60, 1)
   
   [COLOR="green"]'traverse arrays[/COLOR]
   For i = 0 To 3
      [COLOR="green"]'divide seconds by constant, and assign to array as a string[/COLOR]
      period(i) = Format(seconds \ cons(i), "00")
      [COLOR="green"]'calculate the remainder[/COLOR]
      seconds = seconds Mod cons(i)
   Next i
   
   [COLOR="green"]'join the finished array into a delimited string[/COLOR]
   ParseSeconds = Join(period, ":")
   
End Function

 

[namliam]

Too much work.

Convert the number of seconds to hh:mm:ss as follows:

1.
2.
3. Take DOUBLE fraction and use CDate() to convert it to date format.
...then...
4.

Sorry doc but yet to much work... Your CDate step is not needed, date is a double with a mm/dd/yyyy hh:nn:ss format on top of it anyway...
Even the explicit conversion to a double in step 2 is not needed.

And I allways prefer to / 24 hours / 60 minutes / 60 seconds to make it more understandable, rather than devide by 86400 seconds straight off...
Which leaves...

?Format(4/24/60/60, "HH:NN:SS")
00:00:04

 

[The_Doc_Man]

namliam, the Cdate() is needed only because I'm an Ada programmer where using the base of the original typecast isn't legal. One of those habits you get into.

 

[namliam]

Using a value out of its context is "sloppy" sure... I will give you that...
But since dates in Access are doubles anyway....

Ada??? I know a few programming languages, but Ada?

 

[The_Doc_Man]

Ada is a procedural language in the Algol / Pascal family, but is noted for INCREDIBLY strong data typing such that two strings declared of different lengths are considered different, even though both are text strings. Based on Nicklaus Wirth's contention that 80%+ of all programming errors are caused by data mismatches, Ada was designed to catch all data mismatches before they happened. The U.S. Government tends to use it a lot in some applications precisely because of its meticulous data typing.

Ada also allows you to develop "fork" processes, which work GREAT in any CPU and O/S combination that supports single-user, multi-threaded processes. You build a "fork" sub-process and suddenly you have two threads running. When the threads exit, they don't exit, they JOIN (no relation to SQL). When the separated threads have all rejoined, the main process picks up again.

I tend to make somewhat pedantic code snippets precisely because I am emphasizing data types in the given context. But you are right, sometimes I over-specify. Again, a side effect of working with the U.S. Government. If you don't tell government wonks EXACTLY what you want, you'll never get it - but you WILL get that lost shipment of pencils.... excuse me - that's a No. 2 wood-covered manual graphite marking tool with attached mark remover, 8 inch, yellow. For which you will of course need the Graphite Marking Tool point renewal device, rotational, 120v/60Hz.

 

[namliam]

hahahazhahahahahahahahahahaha

Sounds ..... *lost for words*

 

[CoachPhil]

Namliam,

You are the man! works perfectly, thank you

CoachPhil

 

[raskew]

Phil -

Just for fun, give this a try:

Function TimeConvert(ByVal pTimecount As Double, _
                       Optional ByVal pTimeType As String = "s", _
                       Optional ByVal pDisplay As String = "dhns", _
                       Optional ByVal pBooZero As Boolean = True) _
                       As String
'*******************************************
'Purpose:   Convert number of days, hours,
'           minutes or seconds (default)
'           to a string showing any combina-
'           tion of days, hours, minutes,
'           seconds
'Coded by:  raskew
'Arguments: pTimeCount - count of time elements
'           (Opt) pTimeType - defaults to "s"
'           (Opt) pDisplay - defaults to "dhns"
'           (opt) pBooZero - show zero elements
'                 defaults to True
'Inputs:    1) ? TimeConvert(200000)
'           2) ? TimeConvert(200400, "s", "hns")
'           3) ? TimeConvert(200400, "s", "hns")

'Output:    1) 2 days 7 hours 33 minutes 20 seconds
'           2) 55 hours 40 minutes
'           3) 55 hours 40 minutes 0 seconds

'*******************************************

Dim timehold     As Double
Dim i            As Integer
Dim n            As Integer
Dim intAmt       As Double
Dim intervalHold As String
Dim strHold      As String
Dim strSay       As String
Dim strtype      As String

    intervalHold = "dhns"
    timehold = pTimecount
    
    'clarify options
    pBooZero = IIf(IsMissing(pBooZero), True, pBooZero)
    pTimeType = IIf(IsMissing(pTimeType), "s", pTimeType)
    
    'correct common input error (representing minutes as "m", rather than "n")
    pTimeType = IIf(pTimeType = "m", "n", pTimeType)
    
    timehold = timehold * Choose(InStr("snhd", pTimeType), 1, 60, 3600, 86400)
    pDisplay = IIf(IsMissing(pDisplay), "dhns", pDisplay)
    'correct common input error
    If InStr(pDisplay, "m") > 0 Then
       pDisplay = Left(pDisplay, InStr(pDisplay, "m") - 1) & "n" & Mid(pDisplay, (InStr(pDisplay, "m") + 1))
    End If
    
    'verify display sequence
    strHold = ""
    For n = 1 To 4
       If InStr(pDisplay, Mid(intervalHold, n, 1)) > 0 Then
          strHold = strHold & Mid(intervalHold, n, 1)
       End If
    Next n
    pDisplay = strHold
    
    ' ...let it rip!
    For i = 1 To Len(pDisplay)
        strtype = Mid(pDisplay, i, 1)
        intAmt = Int(timehold / Choose(InStr("dhns", strtype), 86400, 3600, 60, 1))
        If intAmt <> 0 Or (intAmt = 0 And pBooZero) Then
           'changed
           strSay = strSay & Format(intAmt, "00")
           'added
           strSay = strSay & IIf(i < Len(pDisplay), ":", "")
           'strSay = strSay & " " & Choose(InStr("dhns", strType), "day", "hour", "minute", "second")
           'strSay = strSay & IIf(intAmt <> 1, "s ", " ")
        End If
        timehold = timehold - (intAmt * Choose(InStr("dhns", strtype), 86400, 3600, 60, 1))
    Next i
    
    TimeConvert = strSay

End Function
'Here is an alternative method, which would display 1530 minutes as 01:01:30
'Format([Minutes]\(60*24),"00") & ":" & Format(([Minutes]\60) Mod 24,"00") & ":" & Format([Minutes] Mod 60,"00")

Bob

 

[namliam]

Bob,

Uhm wow.... 30 odd lines of code for something that can be done in 1 line using the format....
I am speachless

 

[raskew]

Uhm wow.... 30 odd lines of code for something that can be done in 1 line using the format....
I am speachless

Well, I was hoping to get paid by the keystroke.

OK, seriously, I see what you're saying. What I don't see is how you'd differentiate if you dealing with minutes or hours, vs. seconds.

Hopefully the speechlessness was temporary and maybe now you'd clue us in.

Best Wishes - Bob

 

[namliam]

You never need more than one format situation in one column. Sure if you have lots and lots of different conversions going on in one database I can see a use for this function.

In most cases however it should be relatively easy to come up with a one line solution to convert any time to anything you need without much fuss...
i.e.
?format(200000/24/60/60 + 1, "DD ""Days"" HH ""Hours"" NN ""Minutes"" SS ""Seconds""")
02 Days 07 Hours 33 Minutes 20 Seconds

 

[raskew]

OK, I think.

Say you had 8500 minutes. Here are a couple of examples using TimeConvert:

? timeconvert(8500, "n")
05:21:40:00

? timeconvert(8500, "n", "hns")
141:40:00

Using your method, how could you replicate these?

Bob

 

[namliam]

I didnt say it would be pretty

?format(1 + 8500 / 24 / 60 , "DD:HH:NN:SS")
05:21:40:00

?int(8500 / 24 / 60)*24 +cdbl(format(8500 / 24 / 60 ,"HH")) & format(1 + 8500 / 24 / 60 , ":NN:SS")
141:40:00

 

[raskew]

Hi Namliam -

Thanks for that. You've proven it's possible.

I wrote function TimeConvert() in response to a request where there might be a variety of input and/or desired output. It needs a rewrite since I've managed to screw-up the capability to output 'hours', 'months', 'seconds' rather than the formatting you see in my examples (will fix that).

While you've certainly shown it's doable with one-liners, I tend to lean towards solutions that will work in a variety of situations, versus 'situation specific' or 'date specific' unique solutions. Guess it's a matter of likes and dislikes. For me, I can live with a 30-line function, particularly if it will have the possibility of usage in a variety of requirements. Could take your one-liners and put them into a function that would cover a variety of situations/requirements, but it would then be more than a one-line solution.

So there we are!?

Best Wishes - Bob

 

[namliam]

I am not saying your function is bad or anything.... Just saying it is (in a lot of cases) overkill to the max IMHO.

You need good understanding of how dates work in Access to be able to do this, your function enables everyone to work with it.

Perhaps I should make a repository of "on line date conversions" some day...

 

[CoachPhil]

raskew and namliam,

you were both right, raskew your additional line of code;
'Here is an alternative method, which would display 1530 minutes as 01:01:30
'Format([Minutes]\(60*24),"00") & ":" & Format(([Minutes]\60) Mod 24,"00") & ":" & Format([Minutes] Mod 60,"00")

and namliam, you were spot on.

thanks to both of you

CoachPhil