Convert text from excel to proper date in access
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!