I'm an American and only do US programs, so I don't have to deal with these sorts of international issues. Therefore I might not be the best person to answer this, but I'll put out a few thoughts.

It sounds to me, overall, that you are dealing with an international conversion from dates in DMY format to MDY format based on system settings. I have no idea how this is dealt with in Oracle, but in Excel, the commmand to check the value would be

Application.International(Index:=xlMDY)

Which for my US computer, would return True. For a computer in the UK, it would return False, for a UK machine would be on a MDY setting.

Another thing to try is to type a litteral in your VB Editor. For example, if you type in MsgBox #15/1/2003# (intending to mean Jan 15, 2003) on a US based machine -- that is, a machine whose Application.International(Index:=xlMDY) = TRUE -- the VB Editor will immediately change this to MsgBox #1/15/2003#. It will blink right before your eyes. Quite astounding (and distressing). There is no 15th month, so the VB Editor "knows" that the programmer is not American and simply "accidentally" switched the Day with the Month -- and fixes it for him/her.

However, typing in #12/1/2003#, however, will have NO such effect. This is a valid date in the US (Dec. 1, 2003), so if the programmer intended Jan 12, 2003, well, the mistake will not be corrected.

Again, the rules here are dictated by the Application.International(Index:=xlMDY) setting, which unfortunately, is READ ONLY. I've never really dealt with international issues, so I don't know how to CHANGE one's setting. I would guess that it's in the Registery or something, but someone else will have to answer this. (Sorry.)


There remains the possibility that the "error formatted" values with the (mm/dd/yyyy hh:mm:ss) formatting is the problem, but that would seem unlikely to me on the surface. The reason is that a formatting of "mm/dd/yyyy" would invert the MDY order ALWAYS and would not selectively use intelligence in an attempt to convert MDY to DMY as "best it could".

The data you show, shows that 01/07/2003 is flipped to 07/01/2003, but that 14/07/2003 remains unchanged. Again, this is because there is no 14th month: the data can only legally mean July 14th. But 1/7 can mean Jan 7th, or July 1st, depending on if you live in the US or the UK. If Excel's setting is xlMDY = True, as is mine (and as it appears is yours) then the "ambiguity" is "corrected" by flipping whichever DD/MM values it legally can to MM/DD values.


Another approach that may or may not be possible for you is to deal with dates as serial #'s whenever possible. It looks like to me that you are Querying Oracle and posting the resuls into Excel -- so this won't work -- but often when I am doing calculations or database queries where the data is on an Excel Worksheet, I will make an extra column, copy over the dates, and then convert the cells' number format to "General", so that July 17, 2003 turns into 37819. From there, database comparison values (>,<,=, etc...) can run cleaner for me and without ambiguity.

I hope this helps,
Mike

Just to add - I had a lot of problems with this when converting from Excel 5 to 97, and previously when getting round Y2K issues.

Basically Excel interprets dates according to the format of the machine. VBA doesn't seem to follow these rules when interpreting data from external sources. I have found that in these cases it does not seem to matter whether the global settings are US or European, it still has problems when extracting data from text files into Excel through VBA.

I've handled this in the past (particularly SAP data) as follows:
Get the external application to send through the information in dd-mmm-yyyy format.
or
import as text and use combinations of left, mid and right to extract the relevant day, month and year elements. You can use a formula in Excel, which will then interpret correctly, or use code & test the results.

I think you have to change the date settings on Windows and Office applications separately as well, but maybe that's just the setup of my machine...

Hope this helps

Nick

Hi,

I'm have the same sort of issues. I think most poeople that use date in VB out side the us do, but the MS for you!

I seen a lot of pople talking about the problme and the causes, which is basiclly VB code swaping the dd/mm for mm/dd, but only when it can!!

It would be far better if VB just left what the format as it was, then you could chage it your self!

If i find any good soutions then i let you know

I think nedcases' way will get you there but it seam like a lot of hard work, I might just use a integer instead of a date format from my soucre, but excel is poor i this area.

See your point on overkill & would normally be all in favour of easier options. Unfortunately when importing text files you don't always have the option of using numeric dates, hence using LEFT&"/"&MID"/"&RIGHT (as you would use SUBSTR in SQL) with a CDATE (VBA) or DATEVALUE (Excel) function applied to the result is always my preferred option. It may be more difficult, but it guarrantees accuracy.

Any more ideas?

funny enough i've just got round my issue by altering the soruce data:

from dd/mm/yy to dd mmm yy (i.e. 02 Aug 03)

Although my soruce doesn't have - separatior in it excel seams to be picking it up fine (and adding a -)

i then done


Columns("f:f").Select
Selection.NumberFormat = "dd/mm/yy"

and all the dates are correct:

Doesn't really sovle the problem though