Handling Secongs

[Middlemarch]

I found this on the web



For an Access database you can use this:
VB Code:

1. sqltotal = " Select id,Format(sum(use),'hh:nn:ss") from tel group by id "
   
   

Seems interesting so I wondered about

? Format(387, "hh:nn:ss")
or
? Format(sum(387), "hh:nn:ss")

But neither work. Is it possible to use Format to return h:m:s from seconds ?

[Micron]

? Format(387, "hh:nn:ss") works - it just doesn't do what you think it should. Date/time values are floating point double data type where the right side is the fraction of a day after midnight and the left side is the number of days after 1899 (methinks 1901 for Mac). So formatting the number of days (387) since December 31 1899 as hour/minute/second values makes no sense. You would have to multiply the days by 24 to get the applicable number of hours, and that result by 60 to get the number of applicable minutes and so on.
Try 387.50 and see what you get - and if it makes sense.

[Middlemarch]

387.50 gave 12:00:00 which doesn't make a lot of sense (to me).
The 387 are seconds (not days) if that changes things. I wondered if Access would do the arithmetic for me.
Rather than my (poor) attempt which I don't think is quite right...

hr = Int(mySeconds \ 86400)
    min = Int(mySeconds \ 3600)
    min = min & Int(mySeconds \ 60)
    sec = mySeconds Mod 60
    Duration = hr & ":" & min & ":" & Format(sec, "0#")

[June7]

Format is not returning elapsed time, not exactly. It's trying to convert a number to clock time of day. However, time of day is essentially elapsed time from midnight.

Consider Format(387 / 86400, "hh:nn:ss")

Returns 00:06:27


Also, your function code works.

[Middlemarch]

@June7 hey that's great! I didn't think my code in msg 3 was right. It seconds were e.g. 634448 it would return
7:17610574:08
The corrected code is

 hr = mySeconds \ 3600
    y = mySeconds Mod 3600
    min = y \ 60
    sec = y Mod 60
    Debug.Print hr & ":" & Format(min, "0#") & ":" & Format(sec, "0#")

which gives 176:14:08
Format(634448 / 86400, "hh:nn:ss")
=08:14:08. Maths is not my strong point most likely 634448 is way too large. Yes, if I make it 63448
both methods return 17:37:28

[Micron]

You got 12:00:00 because the .50 is half a day, which is 12 hours - especially when you're formatting such a number as time. So I don't think what I was saying had sunk in yet, but maybe it has now: 387 is 387 full days. You might think it was time but on the left side of the decimal, it's days. The right side is a fraction of a day - it's not seconds either. Time duration in hours, minutes and seconds is a bit of a bother in Access.

[June7]

The Format expression only works as long as long as elapsed time is less than 24 hours - 86400 seconds.

Ooops, I only tested your function with 387.

Glad you got it figured out.

[Middlemarch]

Thank you both. Yes I get it... and it'll help tidy up a few things. One more thing not to forget!