Converting mixture of US dates and Euro dates in an access table

[BrockWade]

Does anyone know how to convert a table of dates to US format mm/dd/yyyy when there is a MIXTURE of the two? I have some that are 9/10/2013 and some that are 10/9/2013. I'm doing this in some Access queries. I tried Format([PostDate],"dd/mm/yyyy") AS FormattedDate but get inconsistent results. Thanks all !!

[ItsMe]

Formatting does not have any effect on the underlying data. Formatting will effect how the data is displayed. If the literal value does not have the month first, the date second, and year last, then it can not be recognized by Access as a date data type. When I import dates into my DB's and the dates have, for instance, the year first, I will store the value in a text field or parse the data and change the order around.

Before you can place data into a date field in Access it must list the month first, date second, year last. It must be the American date standard. There are exceptions where Access will translate the value and update the value to the correct date. However, this is not reliable in cases such as 10/9/2013. Where 10 is the date and 9 is the month. Access will translate this value as October 9th, 2013.

[BrockWade]

I tried to do a query that did a CStr to turn the dates into text, but I have no idea how to reliably parse the record for the correct US order. Also, is there a place in the month where a parsing would need to allow for a different parsing code?

[ItsMe]

I have been trying to avoid the word "format", but I am going to use it here now. Before the data is entered into your Access DB, what format is it in? For instance, the country origin needs to be identified so we know what we are dealing with. This "date format" will determine how to parse the data.

The first step is to catch it BEFORE the data gets inside a data field. Can you identify the time and place the data is first being introduced? What is that process like?

[BrockWade]

I upload it from an Excel sheet that comes from a bank... there are no fields that indicate or are clues as to the origin of tha date... the month I am using has data only from September and has several like 07/09/2013 but others like 20/9/2013... some translate correctly and others do not... for some odd reason the translation starts to fail consistently at 13/9/2013 forward.

[ItsMe]

I can tell you for certain that Access will translate some dates correctly and then not others. It depends on whether or not the date appears to be ambiguous. If it looks like an American date, Access will accept it as so and not give it a second look, regardless of the "Date Format", "Country Origin".

You will have to identify the literal date before you attempt to parse the data. Maybe there is another Key value in the spreadsheet that indicates the format/country origin.

[June7]

If the value in the column is 7/9/2013, Access will read it as July 9, 2013. If the value is 20/9/2013, Access will 'fix' it and read it as September 20, 2013 because Access 'knows' there is no month 20.

How is the Excel column formatted? I just realized Date format can have a country parameter. I set it for English(U.K.) and when I typed 7/9/2013, it displays 9/7/2013. I switch to English(U.S.) and the display is 7/9/2013. When I type in 20/9/2013, it doesn't even act like a date, acts like text. I typed in 9/20/2013 and in U.K. format it displays as 20/9/2013, in U.S. format it displays as 9/20/2013.

If the column was General format when values entered, Excel will recognize valid dates exactly as entered. So 7/9/2013 is seen as July 9, 2013. Typing 20/9/2013 is not recognized as a date. This can be seen by the alignment within the column. The date aligns right and the text aligns left.

I entered those dates into General format column. Then I formatted the column as Date U.S. The display was not changed. I formatted it back to General and the 7/9/2013 value displays as 41464 but 20/9/2013 still displays as text 20/9/2013 aligned left.

Also, typing 20/9/2013 into a column formatted as Date produces the same as above, it is not recognized as a date value.

Why would the bank not be consistent in entering dates?

Since Excel and Access both accept 10/9/2013 as valid, there is no way to ascertain that the input was actually meant to represent September 10, 2013, unless you have been explicitly told that all values presented should be for the month of September.

This is a case of GIGO and might not be any way to correct after import.

[ItsMe]

I was curious about how Excel handled it. Interesting about automatically treating some dates as text. I may have to try that out myself.

Maybe the OP can use this behavior to discern the true date of a given cell

[rpeare]

if you are importing only one month of data at a time and you know the month of the import can't you work around it that way and convert all the dates to the month you expect and spit out any 'rejects' if they do not have a month in the expected range? If your process can import multiple months at a time that's more of a problem. I'm thinking about this purely in terms of not having to touch the original excel file if at all possible.