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-16T09:38:42+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:

Hi Hans,

Thanks for your ultra fast response and clear explanations. It worked fantastically. I've never used Power Query before and I'm very impressed. I now need to find more information/tuition about Power Query as it's a powerful tool.

Kind regards
Was this answer helpful?

0 comments No comments
Anonymous

2021-05-16T09:18:09+00:00

Hi.

Thanks for your video. I watched it a few times but it's going too fast for me as I'm a complete novice to Power Query. I think from what I've seen in your video that it 's a very good tool.

Can you send me more articles about using Power Query.

Also, if you have time can you show me in steps stages how to do the following with Power Query:

BEFORE AFTER

03/23/2021 08:00 PM 23/03/2021 20:00

Thanks
Was this answer helpful?

0 comments No comments
Anonymous

2021-05-16T08:07:57+00:00

If you want to be able to use the macro in multiple workbooks, you should store it in your personal macro workbook. See Copy your macros to a Personal Macro Workbook for instructions. The macro will then always be available.

If you don't need the existing other code anymore, you can delete the modules in other workbooks, but that is not essential.

Hi Hans

Are you familiar with Power Query? In my firm not everyone is comfortable with using Visual Basics. A video has been posted on Power Query by a contributor to this site. I've had a look at it, but she's going too fast and I can't implement what she's saying.

I would like to be able to do the following with Power Query.

BEFORE AFTER

03/23/2021 08:00 PM 23/03/2021 20:00
Was this answer helpful?

0 comments No comments