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-14T22:41:26+00:00

    Hi Hans

    I have inserted a Module in Visual Basics and called it "TEXTODATA", It seems  to work, but comes up each time with a "debug message". Is it the correct way of doing it?

    Thanks in advance.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-05-15T01:55:52+00:00

    First thing you have to do is make sure that your data, no matter what format it comes in as, Excel recognizes it as a date and or time data type. 

    .

    Where is the data coming from? 

    .

    How are you getting it into Excel?

    .

    Macros are the "traditional" approach.  You can also use formulas to do the same thing. The "new" method is to use PowerQuery.

    .

    PowerQuery is a tool to import and "clean" data. The import can be from an Excel spreadsheet, or from 80+ sources outside of Excel.  To use it, you tell PowerQuery where to get the data, then you can setup up any number of steps to modify your data into a desired final format.  Data type conversion, specifically dates is one of the common manipulations. 

    .

    Like macros, PowerQueries can be reused on new data any number of times. 

    Here are a couple of articles to show you the possibilities. If you want (more) help getting started in PowerQuery I have more basic articles you can work with.

    @ Easily Fix Dates Formatted as Text with Power Query – Find/Replace text in PQ - Searching for Text Strings in Power Query                    2020 10 21
    https://www.myonlinetraininghub.com/searching-for-text-strings-in-power-query
    https://www.youtube.com/watch?v=0RN3FZv3w84&rel=0           12min47
    This week’s video from Mynda shows us how to easily fix dates formatted as text and addresses a common problem when opening CSV or text files in Excel containing dates that don’t match your region’s date format.
    Power Query makes fixing dates entered as text in Excel super easy, and it's quick to update when you get new data.
    .  *  The query to create a list of words (extract words from a table)
    .  *  The list created by the query
    .  *  Finding Substrings
    .  *  Finds Substrings - Ignoring Case
    .  *  Exact Match String Searches
    .  *  Exact Match String Searches - Ignoring Case
    .  *  PQ M Functions: List.ContainsAny(), List.Transform(), Table.AddColumn(), Table.ToList(), Text.Contains(),Text.Split()
    .

    4 Ways to Fix Date Errors in Power Query + Locale & Regional Settings 2020 04 29              Jon Acampora
    https://www.excelcampus.com/powerquery/power-query-date-errors-settings/
    Learn 4 different ways to fix date data type errors in Power Query, including with locale, regional settings, and custom formulas with Column From Examples. Sometimes in Power Query, when you attempt to format data as a date, you will receive error messages. This is because Power Query is unable to recognize the data. The most common occurrence for this is when the original format of the date is from a different region.
    .  1. Locale in Data Type Menu
    .  2. Locale in Regional Settings
    .  3. Operating System Regional Settings
    .  4. Custom Formula with Column From Examples
    .

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2021-05-15T08:32:58+00:00

    Select the range with dates+times.

    Press Alt+F11 to activate the Visual Basic Editor.

    Select Insert > Module.

    There is no immediate need to rename the new module.

    Copy the code from my first reply into the module.

    With the insertion point anywhere in the code (between Sub and End Sub), select Run > Run Macro or press F5.

    Switch back to Excel to inspect the result.

    When you save the workbook, Excel will probably warn you that macros cannot be saved in a macro-free workbook, and ask if you want to save it anyway.

    If you want to be able to run the macro again later on, click No, then save the workbook as a macro-enabled workbook (.xlsm).

    If you don't need the macro anymore, click Yes.

    Was this answer helpful?

    0 comments