Convert Seconds into HH:MM:SS (1 Viewer)

Arry · Oct 31, 2003

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 · Oct 31, 2003

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 · Oct 31, 2003

would:

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

pdx_man · Oct 31, 2003

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 · Nov 1, 2003

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

Arry · Nov 3, 2003

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

Arry · Nov 11, 2003

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 · Nov 11, 2003

pdx_man said:
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 · Nov 11, 2003

misscrf · Mar 17, 2008

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

pdx_man · Mar 24, 2008

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

DGR · Aug 12, 2009

Just had to post an update to this old question:

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

pdx_man said:
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 · Aug 13, 2009

Yes that is a very nice rounding issue

will1128 · Jan 20, 2010

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 · Jan 20, 2010

Code:

    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 · Jan 20, 2010

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 · Aug 30, 2012

pdx_man said:
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 · Jun 18, 2013

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 · Jul 16, 2013

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.

Convert Seconds into HH:MM:SS (1 Viewer)

Registered User.

Just trying to help

Dim Person

Just trying to help

Dim Person

Registered User.

Registered User.

The Mailman - AWF VIP

Just trying to help

Registered User.

Just trying to help

New member

The Mailman - AWF VIP

Registered User.

Registered User.

Registered User.

New member

New member

Registered User.

Similar threads

Users who are viewing this thread