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-16T22:12:01+00:00

As I suspected, Excel HAS recognized some of the dates as real dates.

The original data source most likely is a CSV file (or some kind of text file exported from another program) which had the original data stored in US format (MDY).

Someone OPEN'd the file in Excel running on a computer where the Windows Regional settings was DMY.

What happens in that situation is that dates where the original D > 12 are seen as Text

If D<=12, the date string is interpreted incorrectly by Excel as being a date in DMY format.

By far, the simplest solution will be to return to the original source, and IMPORT the data correctly, telling Excel that the data is in MDY format, so it will know how to parse the date strings.

Is that possible?

Thanks for your reply. No it's not possible.
Was this answer helpful?

0 comments No comments
Anonymous

2021-05-16T20:58:19+00:00
I've uploaded a sample of the workbook (I've replaced info on the left by cats and dogs)

https://www.dropbox.com/scl/fi/kk8xsnf85n323mvfpshn5/Book1.xlsx?dl=0&rlkey=ly539kkpfqwshmz4xogja4s78

Easy-peasy.
- I downloaded your file, no problem
- Open it in Excel (2010)
- Select all of the rows, including blanks
- applied table formatting
- Rename table with descriptive name (optional, but important for readability!)
- Edited your data to create times other than midnight (highlight in yellow)
- Load data to PowerQuery from table (AUTO FORMAT CORRECTLY RECOGNIZED BOTH DATE FORMATS)
- Close and load to new worksheet
- select the 4 columns of dates
- Right click apply custom format DD/MMM/YYYY HH:MM (personal choice, I prefer text month abbrievation to numeric, I find it easier to clearly and quickly with no confusion separate day from month. Underlying date and sorting remains the same.
Took me (substantially) less than 5 minutes to do it.

Took me MUCH longer to verify and document the process.

Your example data is seriously funky, ie are you using quantum teleporters so that you can arrive in US and UK at same time?

Questions:
1. Your sample data is not in the same format as your original question.
2. All of your example data has midnight time, we can't prove we converted times correctly
3. Your original question stated some data had date and time in separate columns, no examples of that
4. Why would 1 row have arrivals in 2 countries same time, shouldn't each row have depart date/time from one country and arrive in the other at a later date/time
Here is my example file: https://1drv.ms/x/s!Am8lVyUzjKfppkFZLHZOJoFQQoAD?e=kNQlxH
Was this answer helpful?

0 comments No comments
Anonymous

2021-05-16T20:34:58+00:00

As I suspected, Excel HAS recognized some of the dates as real dates.

The original data source most likely is a CSV file (or some kind of text file exported from another program) which had the original data stored in US format (MDY).

Someone OPEN'd the file in Excel running on a computer where the Windows Regional settings was DMY.

What happens in that situation is that dates where the original D > 12 are seen as Text

If D<=12, the date string is interpreted incorrectly by Excel as being a date in DMY format.

By far, the simplest solution will be to return to the original source, and IMPORT the data correctly, telling Excel that the data is in MDY format, so it will know how to parse the date strings.

Is that possible?
Was this answer helpful?

0 comments No comments