Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    American date format (2002)

    I've imported data including a column of dates. The dates are in American rather than UK format. If Excel can treat the date as a valid UK date it does so, otherwise it's left as a text value. For example, 05/06/2007 is formatted as a date but represents 5th June rather than 6th May, whereas 12/20/2007 is left as a text field. How can I convert all dates to a correct UK (dd/mm/yyyy) format? Thanks, Andy.

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

    Re: American date format (2002)

    Do the import again, and specify the date format in the last step of the text import wizard.
    Didn't get the wizard? I bet you imported a csv file. First rename the .csv to e.g. .txt. Then open the file.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: American date format (2002)

    You can also select the data, then run this code:
    <pre>Sub ConvertDatesUStoUK()
    Dim rngcell As Range
    For Each rngcell In Selection
    If IsNumeric(rngcell.Value2) Then
    rngcell.value = DateSerial(Year(rngcell), Day(rngcell), Month(rngcell))
    Else
    rngcell.value = CDate(rngcell.value)
    End If
    Next rngcell
    End Sub

    </pre>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: American date format (2002)

    Thank you. PreferRing note to use code, I presume I could achieve a simliar thing using the Excel functions ISNUMBER and VALUE (or TEXT)? Andy.

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

    Re: American date format (2002)

    With a US date in A1, you could use this formula:

    =IF(ISNUMBER(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1)), DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)))

    and fill down. Rory's code would convert the dates in the same column, the formula converts the dates into another column.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: American date format (2002)

    Thank you.. nice 'sexy' formula! Andy.

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

    Re: American date format (2002)

    I know you prefer not to use code, but some time back I wrote this UDF which takes a second string argument to indicate the format of the incoming data:

    Function convertstringtodate(strSource As String, strFormat As String) As Variant
    ' flexible UDF to handle different incoming string date order, 4 or 6 numbers, removes slashes
    ' specify incoming format as string in second parameter
    Dim strTestDateString As String
    Dim intYPos As Integer, intYLen As Integer, intMPos As Integer, intDPos As Integer
    convertstringtodate = vbNull

    intYPos = InStr(UCase(strFormat), "Y")
    intYLen = 2
    If CBool(InStr(UCase(strFormat), "YYYY")) Then intYLen = 4
    intMPos = InStr(UCase(strFormat), "M")
    intDPos = InStr(UCase(strFormat), "D")

    strTestDateString = Mid(strSource, intYPos, intYLen) & " /" & Mid(strSource, intMPos, 2) & "/" & Mid(strSource, intDPos, 2)
    If IsDate(strTestDateString) Then _
    convertstringtodate = DateSerial(Mid(strSource, intYPos, intYLen), Mid(strSource, intMPos, 2), Mid(strSource, intDPos, 2))
    End Function

    Been a while, so test before relying on.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: American date format (2002)

    This solution has worked perfectly. I used Data/ Text to Columns to split the column of raw data and specified MDY as the column format. So I assume that MDY specifies that the original data is in this format, and then Excel uses the Regional Settings to enable it to convert to UK format? Thank you. Andy.

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

    Re: American date format (2002)

    > So I assume that MDY specifies that the original data is in this format

    Correct

    > Excel uses the Regional Settings to enable it to convert to UK format

    Your regional settings determine the default format Excel uses to display dates, but you can specify whatever you like in the Number tab of the Format | Cells dialog. Excel stores dates as numbers (the number of days since 31 December 1899) and uses the setting you specify to display them on screen.

Posting Permissions

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