Convert Seconds into HH:MM:SS

[Arry]

I have a field in a report which displays a value by total seconds ie 1827 seconds. I would like to convert this value into a new field shown as HH:MM:SS.

Does anyone have any ideas on how i can achieve this.

All help appreciated.

Cheers

Arry

 

[pdx_man]

Well there are 60 seconds in a minute and 60 minutes in an hour ...

Format(Int([Field1]/3600),"00") & ":" & Format(Int(([Field1]-(Int([Field1]/3600)*3600))/60),"00") & ":" & Format((([Field1] Mod 60)),"00")

 

[Fornatian]

would:

Format(([YourField]/3600),"hh:nn:ss") be as effective?

 

[pdx_man]

Trying that, Fornation, I get:
12:10:48

and seeing yours, I see that I dropped a zero in my formula.

Ah, and I see why. 1827 / 3600 yields .5075. So Access is returning .5075ths of a day, or just over 12 hours. So then, we see that we have 86400 seconds in a day. So we modify Fornation's formula to give us:

Format(([Field1]/86400),"hh:nn:ss")

I like that a lot better.

 

[Fornatian]

I knew I was out somewhere, multiplying 3600 x 24hours is what I was actually getting at. Nice one PDX.

 

[Arry]

Thanks for all your help guys. That is great!!!!

 

[Arry]

I am using the formula as per above but when the seconds equal over 24 hours a day is dropped. How can i get this field to show the tot HH:MM:SS and not just the HH:MM:SS of the "current day".

I.e 86412 Seconds should equal 24:00:12 but shows as 00:00:12.

All help appreciated.

 

[namliam]

Well there are 60 seconds in a minute and 60 minutes in an hour ...

Format(Int([Field1]/3600),"00") & ":" & Format(Int(([Field1]-(Int([Field1]/3600)*3600))/60),"00") & ":" & Format((([Field1] Mod 60)),"00")

use something like above....

 

[pdx_man]

Yup

 

[misscrf]

Hey, I was searching for this syntax and just thought I would post a thank you.

 

[pdx_man]

Sure thing. It is always nice to see that our efforts are appreciated.

 

[DGR]

Just had to post an update to this old question:

I have been using the formula posted below for quite a while ...

Well there are 60 seconds in a minute and 60 minutes in an hour ...

Format(Int([Field1]/3600),"00") & ":" & Format(Int(([Field1]-(Int([Field1]/3600)*3600))/60),"00") & ":" & Format((([Field1] Mod 60)),"00")

It worked beautifully until I got a number formatted as 00:00. I played around with my data and discovered that this formula doesn't work when the seconds are > 59.5 or <60. It will format 59.5 as 00:59, and 60 as 1:00. However,59.6, 59.7, 59.8, or 59.9 come out as 00:00.

I found the siimplest solution to be ([datafield]/86400, "hh:nn:ss").

 

[namliam]

Yes that is a very nice rounding issue

 

[will1128]

Would anyone be kind enough to help me figure out how to calculate this so it displays d:h:n:s?

DAYS = Format(Int([Expr3]/86400), "00") - will correctly display days
HOURS = Format(Int(([Expr3])/3600) - ((Int([Expr3]*86400)/3600), "00") - DOES NOT CORRECTLY display correct hours
HOURS = Format(Int([Expr3]/3600),"00") - What will display hours
MINUTES = Int(([Expr3]-(Int([Expr3]/3600)*3600))/60)
SECONDS = Format((([Expr3] Mod 60)),"00")

1054788 seconds should display as 12 days, 4 hours, 59 minutes and 48 seconds. Can't get the hours and day correct. Thanks for all your help!!!

 

[ghudson]

    dTotalSeconds = DateDiff("S", Now(), dChristmasDate)
    iDays = Int(dTotalSeconds / 86400)
    iHours = Int((dTotalSeconds Mod 86400) / 3600)
    iMinutes = Int(((dTotalSeconds Mod 86400) Mod 3600) / 60)
    iSeconds = ((dTotalSeconds Mod 86400) Mod 3600) Mod 60

You can see it in action in my How long until Santa arrives for Christmas? sample. You have to be a member of that site to download samples. That sample also used to be on this site but was lost when the site was hacked a few years ago.

 

[will1128]

Thanks for your help. Finally got it if you need formatting...

DAYS = Format(Int([Expr3]/86400),"00")
HOURS = Format(Int([Expr3]/3600-Int(Int([Expr3]/86400)*24)),"00")
MINUTES = Format(Int(([Expr3]-(Int([Expr3]/3600)*3600))/60),"00")
SECONDS = Format((([Expr3] Mod 60)),"00")

 

[kcushing]

Format(([Field1]/86400),"hh:nn:ss")

I like that format. The problem I have is when it get to over 24 hours it wraps back to 1 instead of showing 25 hours like I want so I went with a combination of the two:

Format(Int(Sum([CallLenth])/3600),"00") & ":" & Format((Sum([CallLenth])/86400),"nn:ss")

This way 91511 seconds shows 25:25:11 which is exactly what I'm looking for.

 

[dbulck]

Another, quicker version of converting seconds to d:h:n:s format:

Format(Int([NbrSeconds]/86400)) & ":" & Format([NbrSeconds]/86400,"hh:nn:ss")


Format(Int(1054788/86400)) & ":" & Format(1054788/86400,"hh:nn:ss") yields the expected 12:04:59:48

 

[kujospam]

I'm getting my information from a calculated field in a query. That has total minutes.for each ticket. I was trying to use Format(([Total Time in Minutes]/1440),”hh:nn”)
But I guess I don't know where to put this. I tried putting it under format. But after I copy it from notepad and save it to format it does this. \in"t([Total Ti"m"e i"n m\in"ute"s\]/"1440),”"hh:nn"”)


Where do I put the format formula? I tried control source also, but that doesn't seem to work either. The calculated field uses datediff to get the minutes in difference. Not sure if that changes anything.