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

Anonymous

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.

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 MVP Volunteer Moderator

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

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.

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.

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

0 comments

Answer accepted by question author

HansV 462.7K MVP Volunteer Moderator

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:

0 comments

45 additional answers

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 No comments
Anonymous

2021-05-14T22:34:48+00:00

Hi Hans

Thanks for your prompt response.

I apologise in advance for my ignorance, but I don't know how to run a Macro. Can you let me know how to do it.

Thanks in advance.
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments
Anonymous

2021-05-14T21:05:17+00:00

Hi Hans

Thank you for your prompt reply. Unfortunately it doesn't work. For unknown reason, Excel doesn't recognise the data as dates.

I have tried formatting the cell to: dd/mm/yyyy hh:mm but nothing changes and the data remain the same (ie American format)
Was this answer helpful?

1 person found this answer helpful.

0 comments No comments