Convert Seconds to Hours Minutes & Seconds (1 Viewer)

A

acombest

New member

Local time

Yesterday, 20:51

Joined

Mar 3, 2011

Messages

2

• Mar 3, 2011

• #1

I have a field that is a monthly total of log on time by seconds. I need to convert to hh:mm:ss. I don't want days displayed, I want the hours to summarize.

For example - 86412 Seconds should equal 24:00:12 but shows as 00:00:12.

Here's what I'm using and its dropping the hours that it converts to days that I don't want it to:

Format$(([ActTbl - Agent_Team_Daily_Totals]![LoggedOnTime]/86400),"hh:mm:ss")

Any help would be greatly appreciated.

 

DCrake

Remembered

Local time

Today, 04:51

Joined

Jun 8, 2005

Messages

8,632

• Mar 3, 2011

• #2

Once it goes over 24 hours it resets the hours element to 00

 

A

acombest

New member

Local time

Yesterday, 20:51

Joined

Mar 3, 2011

Messages

2

• Mar 3, 2011

• #3

Thanks. I FINALLY found what works. Here it is:

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

 

E

ECEK

Registered User.

Local time

Today, 04:51

Joined

Dec 19, 2012

Messages

717

• Jan 6, 2015

• #4

For those who want to cut and paste: There is an error in the preceeding formula (it was missing an open bracket):

Here you go.

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

 

M

mike6271

Registered User.

Local time

Today, 04:51

Joined

Sep 23, 2008

Messages

20

• Jan 7, 2020

• #5

To save anyone the headache make sure to use Int function in every section or it wont work properly.

Like so...

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

 

apr pillai

AWF VIP

Local time

Today, 09:21

Joined

Jan 20, 2005

Messages

735

• Jan 8, 2020

• #6

Here is a User-Defined Function to Calculate and Format Time beyond 24 Hours: Time-Value Formatting Beyond Twenty Four Hours.

 

The_Doc_Man

Immoderate Moderator

Staff member

Local time

Yesterday, 22:51

Joined

Feb 28, 2001

Messages

27,179

• Jan 8, 2020

• #7

Just for clarity for anyone reading this recently reactivated thread:

The problem is due to Microsoft's time formatting routines. You can put together a time format of "hh:nn:ss" and get up to 23:59:59 - but it is part of a "time of day" formatting system. It does not handle elapsed time greater than 23:59:59 because at that point, "tomorrow is another day" (even though it was only a second away.) The custom time-formatting routine is the solution for elapsed time in hours greater than 24.

So far as I know, there is no predefined format specification for hours greater than 24 that is native to Access/Office.