Time formats

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I am importing a file with times operators have spent on the phone.

e.g.
06:20:46
17:13:19
28:42:08

Access doesn't want to import my file as time format so I am trying to
import it as text but it doesn't like that either. I have finally managed to
get the times in but need to work out calculations on them. How can I get
these times into usable numbers and then convert them back again. Something
along the lines of splitting them up and changing to seconds. Not really sure
where to start.

Any help would be greatly appreciated.

Thanks
 
I'm not understanding. I can create a text field in a table and type in
"06:20:46". What isn't "working" when you try to import this as text? Do
you get an error message? If so, what does it say?

I don't know what you are referring to with your notation -- are you
describing hh:mm:ss information? If this is total time spent on the phone,
does that mean, in your third example, that the operator has spent 28 hours,
42 minutes and 8 seconds?

You'll need to build yourself some "parsing" routines to translate these
pieces into a single unit of measure (e.g., how many seconds is 6 hours, 20
minutes and 46 seconds?). Take a look at the mvps.org/access website for
some possibilities.

By the way, you won't be able to convert these into an Access date/time
field value -- this data type records "point in time" information, and it
sounds like you are working with "duration" information.
 
Hello

I wasn't sure what was wrong with the import. I am going to try importing
from a text file next time, I think it was something to do with the Excel
spreadsheets.

The time is hh:mm:ss. It's a weekly total for hours on phone.

FJ

Jeff Boyce said:
I'm not understanding. I can create a text field in a table and type in
"06:20:46". What isn't "working" when you try to import this as text? Do
you get an error message? If so, what does it say?

I don't know what you are referring to with your notation -- are you
describing hh:mm:ss information? If this is total time spent on the phone,
does that mean, in your third example, that the operator has spent 28 hours,
42 minutes and 8 seconds?

You'll need to build yourself some "parsing" routines to translate these
pieces into a single unit of measure (e.g., how many seconds is 6 hours, 20
minutes and 46 seconds?). Take a look at the mvps.org/access website for
some possibilities.

By the way, you won't be able to convert these into an Access date/time
field value -- this data type records "point in time" information, and it
sounds like you are working with "duration" information.
Click to expand...
 
"It's a weekly total for hours on phone."

That's may be the problem. Access (JET) DateTime data type is really an
instance / a point in the date time scale, _not_ duration. Hence, Access
(JET) only recognise time up to 23:59:59.

My guess is that Excel does the same. The only difference is that Excel can
have a column of mixed Date/Time and Text while in Access / JET, the Field
values _must_ be the same data type.
 
Back
Top