I have a curious problem,
I have made an Excel macro command (VB6) with which I run a select query in an Oracle Database, the query returns a line from dates in a Excel Sheet, the date format of the sheet is dd/mm/yyyy hh:mm:ss. The problem is that certain prices are with error format (mm/dd/yyyy hh:mm:ss)
For example if the results from the beginning of this month it would supposed to be like: 01/07/2003,02/07/2003 .10/07/2003 .14/07/2003,15/07/2003
they are appearred to me:
07/01/2003,07/02/2003 ..07/10/2003 ..14/072003,15/07/2003
Its anyone out there that knows why this happen?
I hope you understand me cause English is not my native language.
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
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.
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.
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...
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.