Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Exporting from Adobe produces wrong date format

    I have exported some pages from Adobe and the dates when imported look correct as mm/dd/yyyy. However, if I format them as a number or a date format some, but not all, format as dd/mm/yyyy. Any ideas as to why this might be happening?
    Thanks.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I changed the format of the date cells to d mmm yyyy - see v2 attached.

    They look OK to me - what am I missing ?
    Attached Files Attached Files

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    The locale for the formatting of the dates is set to Chinese. Could this play a part with the behavior you are experiencing?

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi

    If you right-click one of the date cells (in the first posted sample file) to check its format, you will see in the left panel the category is Date, and in the Type panel (on the right) that its format is type *14/03/2001
    As per the note displayed at the bottom of the tab (under the Category panel), "Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in the operating system".

    So something like 05/07/2009 will be treated as 5-Jul-2009 in UK-type systems but as May 7, 2009 in USA-type systems.
    If you have something like 17/2/2010, you can sometimes get errors with USA-type formats because there isn't a 'month number 17".
    This is particularly seen in csv exports with dates.
    Maybe this is what you are seeing.

    And note that Excel VBA ALWAYS assumes USA-format for dates like 05/07/2010 (unless specifically dealt with otherwise) irrespective of whether you are in UK or anywhere else.

    zeddy

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks to all. Since the dataset is reasonable small, my workaround is to visually check the ones that don't have the correct date and then search and replace the excel date number; e.g., 40365.

Posting Permissions

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