Import from Excel: Date Format Locale property

[midmented]

I receive an Excel 2010 spreadsheet that I import into an Access 2010 table using a VBA Function that I wrote. I discovered that some of the dates were not being imported correctly (mm/dd/yy is the correct format). In the Excel cell the date displayed mm/dd/yy but this date imported as dd/mm/yy.

In case anyone wonders, the sheet is generated using Adobe Standard to Save as Other, Excel Spreadsheet.

After sorting through everything, I found that in my Excel sheet date cells, under cells, Number tab, WERE formatted as date but the locale (Location) property is Armenian. (The dates that imported correctly were locale: English (Caribbean)

My question is, how do I reference the Excel cell format locale property to change it to English (U. S.) using MS Access 2010 VBA?

I tried using format([excel cell reference],"mm/dd/yy") but that did not work.

 

[Ranman256]

Are you importing into a DATE type field.
At the same time , import it into a text field.
Then afterwards, parse it into a date field using left/mid functions.

 

[midmented]

I am importing into a date field. My issue with parsing is the data importing is not in a consistent format other than date (locale format could be anything). During import, it could be English (Caribbean) or Armenian. English (Caribbean) imports correctly; Armenian does not.
I am looking for a way to format the locale to English (U. S.) before inserting it into the table.
You have given me an idea to try. Importing it into a temporary table text field and THEN trying to use a format "mm/dd/yy" to update the date field in the active table, using the text field from the temporary table. Seems like a bad work around but if I can't find an answer to the "Locale" issue, I may be trying it.

 

[arnelgp]

dates are represented in double, whatever the locale is. is you are importing it through vba, you can try to create a macro in excel to format that column to EN (us) date., ie:

Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"

 

[gemma-the-husky]

how can you distinguish between date formats if there is ambiguity

12/8/2016 and 8/12/2016 are both valid

the first one is 12th August and the other 8th December

I struggle to see how you can determine which of these is correct for that particular date.

 

[arnelgp]

the cell format is only UI representation of data, if you enter date in one cell in EN, then the other in another locale, you compare their values using =Value(cell), you would get the same value.

 

[midmented]

gemma-the-husky:
Yes, this is what I am struggling with. Because of the locale, both dates are valid. The ambiguity throws another wrench into the issue.

arnelgp:
I will try this approach and see what happens. Maybe the NumberFormat option will solve this issue. I DO know that format "mm/dd/yy" does not because locale evidently is of higher priority.

 

[midmented]

I still can't find a solution to this.

Even in Excel when I click into the cell that contains the Armenian date, the formula bar displays an English date but the cell itself displays as Armenian.

There is a bug in Adobe XI standard. When you "Save as Other" a pdf to Excel, the date columns export in different locales. There is no fix. The only way to resolve this thus far is to physically open the spreadsheet and change the dates manually. TOO many dates to manually do this!
I've approached this on many levels, even by trying to find an Excel formula that would give me a different Locale display. I search for a VBA / Function reference to Locale but there is none I could find.
I WANTED to say "If Locale = "Armenian" then.............
I tried using the =TEXT(cell,"[locale]"mm/dd/yy") which returns dd/mm/yy.

If anyone has ever referenced the Excel cell Locale property successfully, that is what I really need.

 

[midmented]

FINALLY!

I had to think this one through but it works.
I used the NumberFormat function to get the cell format.
Dates with English (Caribbean) came back "mm/dd/yyyy;@"
Dates with Armenian cam back "dd/mm/yyyy;@"
So I simply used an if statement to swap the month and day for Armenian dates.

If WkBk.Sheets(1).Range("C" & i).NumberFormat = "dd/mm/yyyy;@" Then
            dteApplicationExpireDate = WkBk.Sheets(1).Range("C" & i).Value
            dteApplicationExpireDate = Day(dteApplicationExpireDate) & "/" _
                                     & Month(dteApplicationExpireDate) & "/" _
                                     & Year(dteApplicationExpireDate)
        Else
            dteApplicationExpireDate = WkBk.Sheets(1).Range("C" & i).Value
            dteApplicationExpireDate = Month(dteApplicationExpireDate) & "/" _
                                     & Day(dteApplicationExpireDate) & "/" _
                                     & Year(dteApplicationExpireDate)
        End If