Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    20

    Unanswered: curious date results

    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.

  2. #2
    Join Date
    Jul 2003
    Location
    Plainsboro, NJ
    Posts
    11

    Post

    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

  3. #3
    Join Date
    Jul 2003
    Location
    Plainsboro, NJ
    Posts
    11
    Correction:

    Sorry, I had a typo in the above: "a UK machine would be on a MDY setting"

    should have read: "a UK machine would be on a DMY setting."

    -- Mike

  4. #4
    Join Date
    Jul 2003
    Location
    London UK
    Posts
    14
    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

  5. #5
    Join Date
    Aug 2003
    Location
    Northampton, UK
    Posts
    6

    Post Dates in VBA

    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.

  6. #6
    Join Date
    Jul 2003
    Location
    London UK
    Posts
    14

    Arrow

    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?

  7. #7
    Join Date
    Aug 2003
    Location
    Northampton, UK
    Posts
    6
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •