You have a problem in your design. As importing data to a database, each
column (field) should have a proper datatype. Meaning, if you are planning
to actually use the date in a field, all data in this field (vertical data)
needs to be Date formatted (blanks are allowed). The "closed" or some other
informative (vertical) data should have it's own descriptive column deifined
as Text.

So, it seems that you'll need to do some data rearranging in Excel before
the infomation is in database format. You could use formulas and extra
columns to arrange data to correct format, but Access needs a solid range or
a solid sheet (and good to have with proper fieldnames too) as a source to
be properly imported to Access.

Hopefully this helps

Mika Oukka
IT-Consultant

Hi Bill,

You could treat this in Access by defining the Access field as Text and then running an Update query that would remove the non-date entries and then convert the data type of the field to date.  Or in access you could create a query with a calculated field which brings all the text dates into it as dates.  You could also create another calculated column to extract the non dates.

Here is an example of the new field which extracts the dates

Real Date: IIf(IsDate([Date]),[Date],"")

Where Real Date is a calculated new field in the query and [Date] is the text field containing a mix of text dates, text and so on.

Cheers,
Shane Devenshire
Microsoft Excel MVP