Hi, I'm running into a strange problem that I can't get around and was wondering if anyone had any suggestions for me. I have some date and time fields in an excel sheet that display correctly (01/01/2007) even though the value is something like 37564. Same story for two columns that contain times.

When I import them to access, the numeric value of the date and time is imported (e.g. 37564 and .273423423).

How can I convert these to human dates and times?

Here's what I've tried so far:

Re-importing the table --> Access won't give me an option to set the field type for some reason

Changing the properties of the field --> Access deletes all the values when I change the field to date/time format

Changing the field from Date or Time in Excel to "Text" --> converts all the values to 37564 and 0.273452345 type values, instead of human date and time


=TEXT (A11, "hh:mm") in excel --> converts it to military time and I lose AM and PM.


Any suggestions on how I can import this? Thanks!

Bhavesh

Well, I figured out one way to do it....results were a little disappointing, but it worked. I used the Text to Columns feature of Excel, which converted the Dates to text, and the Times to text...only problem is that the times got changed to a goofy hh:mm:ss PM format... e.g. 4:15:00 PM .

Still would be interestedin better solutions if anyone has anything. Thanks.