Excel to Access Date Import Errors

I have tried numerous times to import an excel file into access with a date format of MM/DD/YY. I receive import errors while bringing into Access.

Please advise.
exp vgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi exp vg,

At least tell us what the exact error message is.  That way might get responses from people who aren't clairvoyant, also.

Also, what versions of Excel and Access are you using?
exp vgAuthor Commented:
Access and Excel 2013

I get import errors for other fields, but not the date fields. However, in Excel it does appear in date format, but going to Access the date format is lost.

This may be the issue, but I am not sure how to undo it.

Some of the Excel date fields are self-standing dates - i.e. 1/24/1994

However, some of the other excel dates are sub-categorized by year (i.e. 1994) and then month (January).

Please offer the most efficient way to make all Excel dates into one consistent format.

I have tried selecting the entire Excel column, and then changing the format to date, but this is not working.

In my last post I asked: "At least tell us what the exact error message is.".  What is it?

> I get import errors for other fields, but not the date fields.
So you don't get import errors for the date fields.  I'm confused.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

exp vgAuthor Commented:
Yes, you are correct, I get file conversion errors for the zip code, but not date. However, not all the date cells are imported correctly. As mentioned in my last post, I have an initial take that it may be due to how the dates are stored in Excel.
exp vgAuthor Commented:
It does have to do with how the dates are cateorized/stored in Excel. I separated them out, and the dates that are not under subcategories (i.e. - 1995, January) are able to import successfully.

Therefore, the dates need to be in a single, non-categorized format.
Please explain what you mean by:
  1. "dates that are not under subcategories (i.e. - 1995, January)".
  2. "dates need to be in a single, non-categorized format".
Especially the underlined parts.

Please also provide a copy of your spreadsheet, or a cut-down version of it, which still demonstrates the problem, and indicate in the spreadsheet, which dates import to Access OK, and which fail to.
Gustav BrockCIOCommented:
There are no "subcategories" of dates - whatever those should be.

However, you probably have dates as strings in different an non-standard formats. Then you will have to import these as text and then - using a query - try to convert these expressions to true date values.

First thing is to test with IsDate. If true, DateValue or CDate can convert these; if false, custom parsing must be done to clean up the values.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.