Excel 2010 - change US dates to UK format

Question

I have imported a CSV file with 2 long columns of dates. These dates are in the US format Aug/28/2013 and I want them to be in the standard UK dd/mm/yyyy format.

I have tried formatting the cells as US dates and then converting them to number-only formats, and various other permutations within the Date format box, but with no success.

Can anyone rid me of these awful US dates please?

Answer 1

Assuming that you start with a string (and not an internal excel date number that is just formatted as US format - which is an easy fix), can someone tell me why this method doesn't work?

Use the DATEVALUE / TIMEVALUE functions to convert it into an excel internal formatted date number (You might need to MID() the string in case there are extra bits before or after).

Just make sure that your regional settings match the input date format (otherwise DATEVALUE will fail and you will get a #VALUE error).

Then set the cell format to display the way you want it (Custom format e.g. "dd/mm/yyyy hh:mm:ss").

If you also want to change the timezone, you can add on (hours/24) to the internal Excel excel formatted date number.

Answer 2

The problem is that a US date can parsed by Excel as a UK date when the day is less than 13. When this happens Excel converts it to the localized UK serial (date) number.

So 03/19/2014 is obviously a US date of the 19th of March. However 05/03/2014 is ambiguous so Excel parses it the local date format as the 5th of March, rather than the US 3rd of May. Any formula has to check if Excel has stored the US Date as a UK date. A UK date will be stored in Excel as a number.

=IF(ISNUMBER(A2),DATE(TEXT(A2,"yyyy"),TEXT(A2,"dd"),TEXT(A2,"mm")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2)))

(For a US date in cell A2 and PC date is dd/mm/yy).

If ISNUMBER is true, the US date looks like a UK date and Excel has serialized it as a number. So can format the date as text and back to a date again. Note day is passed to the month parameter of the first DATE function to perform the conversion. If ISNUMBER is false, its stored as a string as Excel doesn't convert a date string with >12 months. So use string functions to split it up for the DATE function.

Answer 3

I tried some of the other suggestions but none seemed to work for me. In my case I was importing US dates in the form M/d/yyyy hh:mm:ss. If you don't mind using some VBA in your spreadsheet then the following function did the job for me:

Public Function USDate(ds As Variant) As Variant
    Dim sp() As String
    Dim spt() As String
    Dim spt2() As String

    If ds = vbNullString Then
        USDate = ""
    ElseIf IsNumeric(ds) Then 
        ' Convert numeric US dates wrongly interpreted as UK i.e. 1/7/2017 as 7th January 2017
        USDate = DateSerial(Year(ds), Day(ds), Month(ds)) 
    Else
        sp = Split(ds, "/") ' split the date portion
        spt = Split(sp(2), " ") ' split the time from the year
        spt2 = Split(spt(1), ":") 'split the time hms

        USDate = DateSerial(spt(0), sp(0), sp(1)) + TimeSerial(spt2(0), spt2(1), spt2(2))
    End If
End Function

Thanks for https://stackoverflow.com/users/845584/peterx pointing out - you will need to create the function in a VBA code module to use this technique.

Simply use it in a spreadsheet formulae for example =USDate(A2)

Answer 4

I'm assuming that the date you received is formatted as text and that simply formatting it as date is not changing anything. You can run the following formula on the date:

=(MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1)&"-"&LEFT(A1,FIND("/",A1)-1)&"-"&RIGHT(A1,4))*1

If you get numbers, you just need to format it as dd/mm/yyyy and it should be good.

Answer 5

Another solution without using a formula:

• Select the relevant columns
• Choose Data → Text to Columns…
• Select “Delimited” and click Next
• Untick all delimiters and click Next
• Select data column format “Date: MDY” and click Finish

The dates should now be converted to UK dates.

Answer 6

There was one more issue for me, as somehow the raw data was supposed to be read as a number, but it did not. Hence, i updated the formula with 1 final case:

=IFERROR(IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2))),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,1)))