Import Excel dates as text

[SherryScrapDog]

I have many Excel files that contain date of birth and date of death fields
that were created by many different people over the years. I want to import
these into Access 2003 and create one database for these. My problem is that
I have different formats of dates, often within the same file. Since there
could be typos in the dates, I don't want to store them as dates; I want to
store them exactly as they are entered. (This is for geneology.) Here are
some examples:
00/000/0000
14/Jan/1878
January 14 1878
14 Jan 1878
14/???/187?
14-Jan-1905 (these are defined in Excel as a custom date and if I change to
text in Excel, they change to Julian date)
--/Jan-1900
These are just a few examples and I'm sure there are more formats I have not
seen yet. Since these dates are for geneologists to see only (and they are
used to seeing any format), and not to actually use as date fields, I think
my best bet is to load them as text. The biggest problem I seem to have is
the 14-Jan-1905 format where Excel has it defined as date; they load as a
Julian date (at least I think that is what the number is). Any suggestions?
Should I be asking in the Excel thread instead? Thanks if you can give me
any ideas to try. Sherry

 

[pietlinden]

blindingly obvious solution to me is to store them as text and then
use date functions on them to convert them to whatever you want.

 

[SherryScrapDog]

I looked up date functions, plus I had read all of the questions/answers I
could find here, and they all appear to talk about converting to a date
format. I assume this could be a simple answer and I do not know how to
write the function.

 

[Douglas J. Steele]

If you import the dates as text, you can either write a query that uses the
CDate function so that you can treat the result as a date, or you can use an
Update query that uses the CDate function to actually write the date to the
table.

Note that all of your dates must be complete dates. If you've got entries
like Jan, 1863, you cannot convert them to dates.

 

[SherryScrapDog]

Hi Doug,
Thank you for your response (and for being kind to me). My really big
problem with this is that I do not have good dates in all of the fields.
There are many question marks because whoever did the cemetery reading did
not know part of the date. What I want to do is the opposite of what most
folks want to do: I want to convert the dates to text. The dates that are
not formatted in Excel as dates are fine. It's the dates that Excel has
formatted as dd/mmm/yyyy that are giving me the problem. When I change the
Excel format to text or general on those, they change to a number (which I
think could be julian). When I import them, they import the number instead
of the date in the cell. I'm beginning to think I may need to retype these
dates as text. Anyway, thanks much for responding! Sherry

 

[Douglas J. Steele]

Actually, the number may simply be how VBA stores dates: as an eight-byte
floating point number where the integer portion represents the date as the
number of days relative to 30 Dec, 1899, and the decimal portion represents
the time as a fraction of a day. Today is 30 Jun, 2008, which is stored as
36929. 06:00 today was 36929.25, Noon was 36929.5, 18:00 was 36929.75 and so
on.