Importing dates pre 1900 (1 Viewer)

[Cotty42]

Hi all

I am trying to import data from Excel into Access to create new tables.

However in some of the excel spreadsheets I am importing there are dates which precede 1900 and, although the data type is set to short date, for some reason excel decides to store these as text, so when I import them I get the dreaded importerror table created and then have to go through the table to manually enter all of the missing dates. Not really a problem when there are only a few but when there are several hundred it becomes a bit of pain.

Is there a way of overcoming this so that I can import the data automatically. I've tried converting the data type in excel to either general or text but when I do the post 1900 dates convert to a number.

Many Thanks

Dave

 

[Uncle Gizmo]

Import the dates as text and then convert them to dates.

 

[Cotty42]

Thanks UncleGizmo

That worked for importing the pre 1900 dates but the post 1900 dates were imported as a number, i.e. 14167, 8874, 14153, etc. (I assume this is the date serial number).

 

[Uncle Gizmo]

What you could try is the first entry in the spreadsheet, the date column, make sure that entry is text.

For example add an extra row, and put text in that, like "Text Date!"

The import will then treat all the rest of that columns entries as text.

Caveat:- (I used this trick about 10 years ago, I assume it still works)

 

[CJ_London]

If the date 'number' is 1 this is the equivalent of 31/12/1899 when using the CDate function - i.e. CDate(1)

CDate(-1) is 29/12/1899
CDate(-2) is 28/12/1899
CDate(14167) is 14/10/1938

Interestingly CDate(0) is 0. not a date although it should be 30/12/1899

 

[Cotty42]

Thanks UncleGizmo

As you suggested I inserted an extra column and used this formula '=TEXT(D376,"dd/mm/yyyy")' to copy the dates into the new column.

Imported the data into Access as a text and then converted back to a Short Date, which works fine. (How come pre 1900 dates are OK in Access but not in Excel?).

Thanks for your help.