Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates in mixed formats (XL2000)

    When I download statements from my bank, dates before the 10th of the month appear in US format, dates from the 10th appear in UK/Aust format. Eg 9th of Aug appears as 8/09/03. 10th of August as 10/08/03. I'm trying to devise a formula or macro to convert the US format dates to UK format, but no go so far.

    I've tried dismantling and reassembling the date using LEFT, MID, RIGHT, but dates appear to be impossible to deal with in this way, nor can I turn them into purely text strings.

    Can anyone help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates in mixed formats (XL2000)

    I assume that dates with day number 13 and higher are interpreted as text values, since 13/08/03 is not a valid US date. Am I correct? If so, you can distinguish those dates from the rest by using ISTEXT.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in mixed formats (XL2000)

    No Hans

    Days 10...31 appear in UK format, days 1..9 in US format; even 10/08/03, which is valid in US or UK format, appears correctly as 10th of August when I change the format to dd mmm yyyy. Perhaps I should have mentioned the file is downloaded as a CSV file, and days 1..9 do not have a leading 0, ie they do not appear as 01...09. I think the problem must arise when the file is saved as an XLS file. Maybe dates in CSV format can be manipulated, I'll try it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates in mixed formats (XL2000)

    I'm sorry, Michael, I hadn't looked at your profile, and automatically assumed you were in the US. If the dates are in column A, starting with A1, enter this formula in another column:

    =IF(DAY(A1)<10,DATE(YEAR(A1),DAY(A1),MONTH(A1)),A1 )

    and fill down as far as needed. HTH.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in mixed formats (XL2000)

    Hans

    It turns out that the dates download as a fearful mess of text and date formats, see attachment. With the help of your suggestion I finally came up with the formula:

    =IF(ISTEXT(A2),DATEVALUE(A2),DATE(YEAR(A2),DAY(A2) ,MONTH(A2)))

    Many thanks

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Dates in mixed formats (XL2000)

    When I open this with my default US settings, my following suggestion gets munged, however with your UK settings your formula should successfully resolve to:

    =IF(ISTEXT(A2),DATEVALUE(A2),A2)

    ... but test it first!
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in mixed formats (XL2000)

    Like you John I thought that would be so, but it does not work that way. Somehow XL interprets DAY(A2) and MONTH(A2) the way I want it but gets it the "wrong" (from my point of view) way round when it just formats A2 in date format even when the regional settings are UK. I do not understand why.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in mixed formats (XL2000)

    Another thing to try is to rename the csv to txt and use the text import wizard to open the file. You can then specify which date format your file is in in one of the last steps of the wizard.

    If you use Data, get external data, Import Text file, all you have to do with a new file is click "Refresh data" (maybe after renaming, not sure if that is needed) whilst on the sheet you last imported the data into. XL will prompt for the filename and off you go.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates in mixed formats (XL2000)

    Thanks Jan, I'll have a look at that next time I download.

    It's amazing how many items there are on menus that one sees often and never asks "I wonder what that does", for example Get External Data.

Posting Permissions

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