Vfrdtyky

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.

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.

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.

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)

Related to this, the below simply formula can be helpful for changing a date from

"MM/DD/YYYY"

into

"DD/MM/YYYY".

=VALUE(TEXT(B2,"mm/dd/yyyy"))

We can get best of both world with this more concise formula:

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

Can't find anything shorter.

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)))

This can be tricky when the dates in mixed format eg. UK and US in the same column. I have found an effective if inelegant solution:

Step1) Select the column containing the dates to be converted;



Step2) Format, Cells, Text;



Step3) Format, Cells, Date, US;



Step4) Data, Text to column, Next, Delimited, Next, delete all delimiters, Next, select format MDY;



Step5) Format, Cells, Date, UK.

Step4 had been suggested elsewhere, but that on it's own didn't do it for me. I am hoping to combine these steps into a macro but no success this far.

I couldn't get the most common answer to work, the process that worked for me was:

For date 10/04/2018 11:49:20, right-click cell and "Format Cells", "Number" tab and select "Custom" Category and then select mm/dd/yyyy hh:mm.