import excel dates into access

[bookouri]

I have an excel worksheet with a column of dates that are entered like 10/10/1999. When I import this data into Access all the dates become calculations, i.e. 10 divided by 10 divided by 1999. Ive tried formatting the excel columns as text and as dates but it seems to lose that when access imports it. I could go cell by cell and put a ' in front of each date and preserve the formatting, but there are too many rows.

How can I import the excel dates as plain text '10/10/1999'

thanks for any suggestions..

 

[SkipVought]

Hi,

If the dates are really DATES in Excel, they ought to import as dates.

The way to determine this is to format any cell in Excel containing one of these "dates" as GENERAL. The FORMAT should CHANGE to a NUMBER like 34567.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bookouri]

in excel the cells are formatted as dates so they show up properly. I dont understand why access wont format them properly. In fact, normally you can change the format of each column on the fly when importing into access, but when importing the excel worksheets the data type is grayed out in the access import dialog..

i can probably export the excel to text and then import to access, but im hoping there's a trick to avoid that..

thanks for the reply

 

[SkipVought]

My Access 97 imports dates as dates ???

What format does Access show in gray for the date column?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bookouri]

access defaults to text, and in the preview pane you can see the excel dates have already been converted to plain numbers i.e. the result of the 10 divided by 10 divided by 1999 etc...

 

[bookouri]

BTW im using office 2000

 

[TropicalFred]

Try this:

Open your Excel spreadsheet. Click on Tools > Options.
Select the Transition tab. Under Sheet Options uncheck Transition formula entry. Clcik OK. Save worksheet. Import to Access. Hope this helps.

TropicalFred

 

[bookouri]

the transition formula entry is already unchecked. I looked around for any other options that might be related but couldnt find anything else...

it was a good try though...