Upload Excel Sheet to Access: Date Format Problem

[colbyhynes]

Hi All,

I am trying to upload an excel file that contains dates in text format like this: yyyy mm dd (eg. 2012 03 14)

I am getting a 'type conversion error' when I try to specify this data as a date.

Does anyone know of a quick macro I can run that will take my text (eg. 2012 03 14) and convert it to a more acceptable excel & access date format? (eg. 14-Mar-12)

Thank you, any help is (as always) greatly appreciated.

p.s. I have tried to just use the 'format cells' option in excel and choose a date format, but my data does not change.

 

[lameid]

Your data appears as text to both Excel and Access and neither knows what to do with it. I used <Somedate> to represent your text date field below... These are Access solutions as this is an Access forum. Both are expressions to return the date for the text field... Likely, you would use this in an update query.

DateSerial(mid(<Somedate>,1,4),mid(<Somedate>,6,2), mid(<Somedate>,9))

cdate(Replace(<Somedate>," ", "-"))

 

[colbyhynes]

LameID... you have saved me from a severe 'CASE OF THE MONDAYS'!

I ran this query:

SELECT cdate(Replace([Somedate]," ","-"))
FROM TableName;

...and it has worked perfectly! Thanks again!