convert hh:mm:ss to seconds

[TONTA]

Hi

This is my 1st post so please be gentle

I am running an Access Database for a large uk companys call centre.. I use many diffrent systems which allow me to show our centres productivity...

I have hit a wall

When i first developed the DB i used the time formatting hh:mm:ss but later down the line i found if you *the time by 86400 it converts the hh:mm:ss into a workable seconds format....

One of my old extracts that i feedinto the DB are in the old format therefor i do this calculation within an access query which *the time by 86400

however i have found an probelm.. if the time is over 24hrs it seems to double the time there or there abouts in seeconds

example

01/01/1900 01:42:00 which is basically 25 hrs should be around 92520 seconds

when is do this calculation in an access query it is coming out 178920 all the other times are correct so this rules out how i link 2 querys ....

any ideas???

 

[neileg]

But Access starts counting at 30/12/1899 so 01/01/1900 01:42:00 is actually 2 days 1 hour and 42 minutes, or 178,920 seconds.

 

[neileg]

Just thought, I bet you expected Access to use the same base date as Excel! But it doesn't...

 

[The_Doc_Man]

TONTA... your problem is an assumption that you made that turns out to not really be true. It happens all too often.

DATE data type is NOT as simple as it looks. Mathematically, it includes a bias (some prefer the term offset). A DATE field is the number of days and fractions thereof since midnight of the reference date, which is now over 100 years ago. (See earlier post this thread for the date). Thus, the bias is now greater than 36525 days. The good news is that a DATE field still tracks time to about 0.0001 seconds when using this format. Of course, unless you "cheat" on the format, you can't see the fractions in Access anyway. But your system clock probably gives you at least 0.01 and maybe 0.001 seconds accuracy in such numbers if you use the NOW() function.

If you want to manipulate times that eventually could exceed 24 hours (even it could happen only ONE TIME), don't store that accumulating or totaled time in a "standard" DATE format. Convert all times to something you like such as, say, integer minutes or integer seconds or floating-point days & fractions. But be aware that you will need to write a formatting routine - and probably an input routine as well.

If you do decide to write something, get out your VBA books and write two public functions - one a STRING output to give you hhhh:mm:ss format or dddd-hh:mm:ss format when given your preferred time accumulation units, and the other to give you back those units when given a string in the aforementioned format. If you do that, you can add your times, accumulate work increments (I work around a help desk, too, so I know why you are doing what you are doing), etc.

If I had my way, Access would either NOT have a DATE formatted data format at all, or it would have TWO such formats - one an ABSOLUTE DATE and the other an ELAPSED TIME. Though they would be compatible, they would surely be different. But as we know, Access is a Bill Gates product...

Search this forum for topics that include the string "hh:mm" or "Adding Times" to see other viewpoints on this tricky subject.