Excel - How to convert US dates and times to UK dates and times

That means that the values aren't real dates, but text values that look like dates.

1. For a range with separate dates in a column:

• Select the range.
• On the Data tab of the ribbon, click Text to Columns
• Click Next> twice.
• In step 3 of the Text to Columns Wizard, select Date, and select MDY from the drop down.
• Click OK.

2. For a range with separate times in a column:

• Select an empty cell and copy it.
• Select the range with times.
• Click the lower half of the Paste button and select Paste Special...
• Select Add, then click OK.

3. For a range with dates+times in a column:

• Select the range.
• Run the macro listed below.
• You can discard the macro afterwards.

Sub Text2Date()
    Dim rng As Range
    Dim s As String
    Dim p As Long
    Dim d As String
    Dim t As String
    Dim a() As String
    Application.ScreenUpdating = False
    For Each rng In Selection
        s = rng.Text
        p = InStr(s, " ")
        d = Left(s, p - 1)
        a = Split(d, "/")
        t = Trim(Mid(s, p + 1))
        rng.Value = DateSerial(a(2), a(0), a(1)) + TimeValue(t)
    Next rng
    Selection.NumberFormat = "dd/mm/yyyy hh:nn"
    Application.ScreenUpdating = True
End Sub

I don't use PowerQuery much, so if the following is off, I hope that others will take me to task - criticism is welcome!

Try this:

Click in the column with dates-as-text.

On the Insert tab of the Ribbon, click Table.

Make sure that 'My table has headers' is ticked, then click OK.

On the Data tab of the ribbon, in the Get & Transform group, click 'From Table/Range'.

PowerQuery will automatically recognize the values as date/time and display them in your system date/time format (mine is yyyy-mm-dd hh:mm:ss)

Click Close & Load.

The result is a new table with 'real' date/time values:

You can now apply the desired format: