Results 1 to 4 of 4

Thread: US to UK dates

  1. #1
    Lounger
    Join Date
    Mar 2005
    Posts
    27
    Thanks
    1
    Thanked 0 Times in 0 Posts

    US to UK dates

    Hi All - Excel 2013

    I have an issue whereby a lot of the data I receive comes in the American format of mm/dd/yyyy which I need to transfer into a UK date of dd/mm/yyyy.

    Currently I am changing them by hand - as the format on the US data is custom as opposed to date - any suggestions ?

    cheers in advance

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    bobski
    Not sure what you mean by custom? If the date is entered as text by example 09/26/2014 for September 26, 2014
    Use the funciton datevalue() which will turn the text back to a serial number. Then just format that serial number using the date format native to your Excel and you should see the date just the same way as all other cells formated for dates.

    If however the custom format is something other than text can you please post a sample of what you receive so a solution can be posted.

    TD

  3. #3
    2 Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    109
    Thanks
    12
    Thanked 3 Times in 3 Posts
    Highlight the cells in question and right click, select Format cells, then customise.
    Enter the format you want i.e. DD-MM-YY and click OK.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    The following macro converts date strings and values in the selected range between mm/dd/yyyy and dd/mm/yyyy. The output is in the form of date values that you can format as dates via Format|Cells|Number|Date.
    Code:
    Sub ConvertDateFormat()
    Dim DtRange As Range, oCell As Range, oTxt As String
    If Selection.Cells.Count = 1 Then
      Set DtRange = ActiveCell
    Else
      Set DtRange = Selection
    End If
    With Application
      On Error Resume Next ' In case there are no xlConstants or convertible dates in DtRange
      For Each oCell In DtRange.SpecialCells(xlConstants)
        oTxt = oCell.Text
        If UBound(Split(oTxt, "/")) = 2 Then _
          oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/" & Split(oTxt, "/")(2))
      Next oCell
    End With
    End Sub
    Note that no conversion is performed if the date is embedded in a formula or the output would be an invalid date.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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