Struggling with Date format while importing / exporing from-to Excel

[Ashfaque]

Hi,

I have one date column in my Excel file which is by default in American date format i.e. month/day/year.

7/2/2016 8:47 AM
7/2/2016 3:00 PM
7/3/2016 8:54 AM
7/3/2016 3:00 PM
7/4/2016 8:54 AM
7/4/2016 3:08 PM

I want to get it imported into my access table thru vba code that has been a successful practice but with first 12 dates are being imported with American format and from 13 onwards, it imports as per desired date format (dd/mm/yyyy)

My access tbl field is Date/Time format.

I want import this date from field from Excel sheet in correct format (dd/mm/yyyy).

Because when I am exporting to a pre-designed Excel sheet, these first 12 dates are being exported to earlier format which is mm/dd/yyyy and the from 13th onwards they are correctly being exported.

This has became big stuck to me.

Any idea to imported date in dd/mm/yyyy format while importing to tble.

 

[sneuberg]

A forum member had a similar problem in this thread in that he was importing dates in American format but needed them to end up in the format of his regional settings or dd/mm/yyyy format. He also had the additional problem of extraction the date from a string. Ignoring that part of the problem I believe the expression for converting was simply:

ConvertedDate: CDate(Format([AmericanDate],"mm/dd/yyyy"))