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

Anonymous
2021-05-14T17:03:30+00:00

Hello

I wonder if I could receive help with the following:

I have a long Excel spreadsheet where the dates and times are in US format and need to be changed to UK format. The problem is that the date and time need to be merged into one cell.

BEFORE HOW IT SHOULD LOOK
06/03/2021 06:PM 23/06/2021 18:00

Sometimes the data is laid out as below:

DATE TIME HOW IT SHOULD LOOK
06/03/21 06:PM 23/06/2021 18:00

Thanks for your help.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2021-05-14T21:28:56+00:00

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.
  1. 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.
  1. 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

Was this answer helpful?

30+ people found this answer helpful.
0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2021-05-16T08:48:31+00:00

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:

Was this answer helpful?

10+ people found this answer helpful.
0 comments

45 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2021-05-16T11:30:45+00:00

    Power Query seems to have a problem handling null value. If there are blank rows within the range it "muddles" what comes after the blank row. (Incidentally the blank rows need to stay in place)

    This is an example that I've just run on Power Query. I think that the range needs to be continuous and not contain any blank rows for it to work.  As you can see the fourth row "10/02/2017 00:00" has not been changed, because it follows a blank row.

    BEFORE AFTER
    9/19/2018 12:00 AM 19/09/2018 00:00
    9/19/2018 12:00 AM 19/09/2018 00:00
    10/02/2017 00:00 10/02/2017 00:00

    OK, I realize now I was not clear about this problem in my earlier reply. 

    YES!

    When importing data into Power Query, you are correct, it cannot handle completely blank rows.

    You have to remove the completely blanks rows, and blank columns, before you import the data into PowerQuery.

    That is why I prefer to start by defining the data as an Excel table using the <CTL><T> shortcut. This will quickly identify blank rows/columns for you to delete, ie:

    • use<CTL><T> to define a table
    • if you find it does not include all of your data, delete the blank row or column
    • Convert the table back to "normal"
    • define table again. This will include more data
    • Keep repeating until all data is included in table
    • NOW you can import the data into PowerQuery to do data cleaning, like converting text to date data type
    • after you close and save you may still find data errors, either correct / edit the input data (remove exceptions), just return into PowerQuery to correct those exceptions. One way to correct exceptions is to use the "find and replace" feature in PQ so that the fixes are not manual in future data)

    Note: "Find and Replace" is "hidden" in PowerQuery under a different name (thanks for nothing MS!)

    Transform tab > Any Column group > Replace Values drop down > Replace Values or Replace Errors. 

    Image

    Replace Values in Power Query           2019 03 01
    https://yodalearning.com/tutorials/learn-how-replace-values-power-query/
    There is a cool feature of Power Query which is known as ‘Replace Values’ feature. As the name suggests, it can replace any value with the new value you provide into your data table. You may go through step by step procedure where our Power Query Training **** expert has explained the topics in a detailed manner with examples that will help you to understand the concepts better.
    We have a data set as shown in the steps below. We need to change the Country of a person as he has shifted from India to the U.S. Instead of changing the entire data table, we can directly make changes in the Power Query as explained in the steps below.
    .  Step 1: Select the data for replacing
    .  Step 2: Select Replace Values Option
    .  Step 3: Insert New Value
    .  Step 4: Close & Apply
    .

    oops, I just found another place to access "Find and Replace" I don't know if there is any functional difference between the two (I suspect they are the same):

    Home tab > Transform group > Replace Values button

    Image

    BULK Replace Values in Power BI / Power Query   2017 07 01
    https://www.youtube.com/watch?v=MLrRlPh_ZFQ

          9min16
    .
    <edit>

    I removed the last 3 links, they were showing blanks for me too. But not to worry. If you go to the above link, those 3 additional links are on that page and they appear to work. from there

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-05-16T11:43:28+00:00

    Hi

    Thanks for your reply. I Absolutely cannot delete the blank rows.  Basically there are 3 columns of data in one sheet. Some columns have got blank cells but not all 3 columns. I'm working column by column and after formatting the data in Power Query, the data needs to be pasted in the exact position (including blank rows).

    Was this answer helpful?

    0 comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2021-05-16T11:45:11+00:00

    @Rohn007: the last three videos show 

    for me. It might be my location (I'm in Europe)

    Was this answer helpful?

    0 comments