Importing .xls mins/secs (mm:ss) into MS Access 1

[AlienX69]

What is 03:23 in Excel is being converted to "0.0023479861111" during the import into MS Access 2003.

When trying to Import a Excel file containing a column where the format for ALL the cells in that column are Cat: "Custom" Type: mm:ss.

When I step through the Import process and get to "Field Option", the field is "Length", but the Data/Type: is GREYED out and displays Date/Time.

Assume it is being taken as "General" or "Text", if I select a single cell and change format to "General" I get the a value of "0.002347986" for 03:23.

Also tried changing Cat:/Type: to Time/13:30:55, had effect during the Import process.

So my question is how do I maintain the mm:ss format in the Excel file, or something similar I can work with, i.e. 00:03:23. Guess I need more understanding of how MS Access treats/defines each of the Excel column(s) Data/Type.

Thank in advance for any assistance.

AX

 

[AlienX69]

One more thing I forgot to mention, when done with the process the following "12:03:23 AM" is displayed in the table for "03:23". Guess I can work with this and truncate in the qry/report.

PS: I couldn't figure out how to edit my post once it was posted so I could add the above, so I created another post

Regards,
AX

 

[AlienX69]

Here's my best solution.

In a qry I used the following expression:

Time: Mid([Length],4,5)

Is this the proper way ?




"Everyday is a new learning experience"

 

[PHV]

0.002347986 is #1899-12-30 00:03:23#
So, you may simply use this:
Time: Format([Length], "nn:ss")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AlienX69]

Why I thought (and tried) "mm:ss" would work is beyond me. I mean NN makes more sense for "Minutes", why would anyone try MM, stupid me.... ;-)

I never had to express TIME till now, worked with dates a little.

HH:NN:SS ..... well it's permanetly embeded now !!

I did try Googling, most dealt with dates, didn't get the right hit till you keyed me on NN.

THX ... PHV !!!