Converting date values from Excel into Access

[davidrsutton]

Afternoon.....

I am creating a table from data imported from an Excel spreadsheet by using the Import data function in the tables window. This has been fine except that the dates that are stored in Excel are stored as a 5 digit number but formatted to show as a date (dd/mm/yyyy). When you import the dates into Access as a table, you get all the 5 digit numbers rather than the dd/mm/yyyy format which we are after.

Does anyone know of a way to convert these 5 digit date references into a normal date format in Access? Failing that, how you can convert the date formats to text/general in Excel in such a way that you keep the dd/mm/yyyy format?

We are running Access 97 and Excel 97.

 

[PHV]

A starting point:
MsgBox CDate(38482)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[davidrsutton]

Cheers PHV... but could you elaborate a bit morte please!

Cheers!

 

[lespaul]

storing and display of dates are two different things. The 5 digit number you are seeing is the number of days since 12/31/1899, if there is a decimal with additional numbers after it, that is the time of that day.

the formatting of the date value is where you determine if you want to see mm/dd/yyyy or mm/dd/yy or m/d/yy, those are all differing formats of the date.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual