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-16T22:57:24+00:00

    I still don't know what you want.

    Take the first 8-10 rows in the example file and manually type here what you want the end result to look like.

    .

    Do you want/need time zone conversion between US and UK?  Which time zones?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-05-16T23:41:05+00:00

    I still don't know what you want.

    Take the first 8-10 rows in the example file and manually type here what you want the end result to look like.

    .

    Do you want/need time zone conversion between US and UK?  Which time zones? 

    Hans from this website gave me quickly a working solution to this and has saved the day. The work is now completed and everyone in my firm is happy.

    I shall leave it at that.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-05-16T23:44:00+00:00

    Here is a VBA routine that properly converts the US dates to UK dates.

    This is adapted from a more general purpose routine I wrote some years ago, and I have made only minor alterations, so there is some extraneous code not strictly required for this purpose.

    Assuming that the original data was US dates; and that your system regional short date format is DMY, this routine seems to do what you want.

    Starting with the columns A, B, C from your workbook, it creates columns D,E with the UK dates

    Note that this not only turns the "text" date representations into UK dates, but also corrects the incorrect conversion of some US dates that had already occurred.

    To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.

    Ensure your project is highlighted in the Project Explorer window.

    Then, from the top menu, select Insert/Module and

    paste the code below into the window that opens.

    To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

    Option Explicit

    Sub ConvertToUKDates()

    Dim R As Range, C As Range

    Dim sDelim As String

    Dim FileDateFormat As String * 3

    Dim I As Long, J As Long, V As Variant

    Dim vDateParts As Variant

    Dim YR As Long, MN As Long, DY As Long

    Dim TM As Double

    Dim vRes As Variant 'to hold the results of conversion

        Dim colNum As Long

        For colNum = 2 To 3 'convert columns B and C

    Set R = Intersect(Cells(1, colNum).EntireColumn, ActiveSheet.UsedRange)

    ReDim vRes(1 To R.Rows.Count, 1 To 1)

    'Find a "text date" cell to analyze

    For Each C In R

        With C

        If IsDate(.Value) And Not IsNumeric(.Value2) Then

            'find delimiter

            For I = 1 To Len(.Text)

                If Not Mid(.Text, I, 1) Like "#" Then

                    sDelim = Mid(.Text, I, 1)

                    Exit For

                End If

            Next I

            'split off any times

            V = Split(.Text & " 00:00")

            vDateParts = Split(V(0), sDelim)

            If vDateParts(0) > 12 Then

                FileDateFormat = "DMY"

                Exit For

            ElseIf vDateParts(1) > 12 Then

                FileDateFormat = "MDY"

                Exit For

            Else

                MsgBox "cannot analyze data"

                Exit Sub

            End If

        End If

        End With

    Next C

    If sDelim = "" Then

       MsgBox "cannot find problem"

       Exit Sub

    End If

    'Check that analyzed date format different from Windows Regional Settings

    Select Case Application.International(xlDateOrder)

        Case 0 'MDY

            If FileDateFormat = "MDY" Then

                MsgBox "File Date Format and Windows Regional Settings match" & vbLf _

                    & "Look for problem elsewhere"

                Exit Sub

            End If

        Case 1 'DMY

            If FileDateFormat = "DMY" Then

                MsgBox "File Date Format and Windows Regional Settings match" & vbLf _

                    & "Look for problem elsewhere"

                Exit Sub

            End If

    End Select

    'Process dates

    'Could shorten this segment but probably more understandable this way

    J = 0

    Select Case FileDateFormat

        Case "DMY"

            For Each C In R

            With C

                If IsDate(.Value) And IsNumeric(.Value2) Then

                'Reverse the day and the month

                    YR = Year(.Value2)

                    MN = Day(.Value2)

                    DY = Month(.Value2)

                    TM = .Value2 - Int(.Value2)

                ElseIf IsDate(.Value) And Not IsNumeric(.Value2) Then

                    V = Split(.Text & " 00:00") 'remove the time

                    vDateParts = Split(V(0), sDelim)

                    YR = vDateParts(2)

                    MN = vDateParts(1)

                    DY = vDateParts(0)

                    TM = TimeValue(V(1))

                Else

                    YR = 0

                End If

                J = J + 1

                If YR = 0 Then

                    vRes(J, 1) = C.Value

                Else

                    vRes(J, 1) = DateSerial(YR, MN, DY) + TM

                End If

            End With

            Next C

        Case "MDY"

            For Each C In R

            With C

                If IsDate(.Value) And IsNumeric(.Value2) Then

                'Reverse the day and the month

                    YR = Year(.Value2)

                    MN = Day(.Value2)

                    DY = Month(.Value2)

                    TM = .Value2 - Int(.Value2)

                ElseIf IsDate(.Value) And Not IsNumeric(.Value2) Then

                    V = Split(.Text & " 00:00") 'remove the time

                    vDateParts = Split(V(0), sDelim)

                    YR = vDateParts(2)

                    MN = vDateParts(0)

                    DY = vDateParts(1)

                    TM = TimeValue(V(1))

                Else

                    YR = 0

                End If

                J = J + 1

                If YR = 0 Then

                    vRes(J, 1) = C.Value

                Else

                    vRes(J, 1) = DateSerial(YR, MN, DY) + TM

                End If

            End With

            Next C

    End Select

    'write the results 2 columns to the right of original column

    vRes(1, 1) = Replace(vRes(1, 1), "US", "UK")

    With R.Offset(0, 2)

        .Value = vRes

        .NumberFormat = "dd/mm/yyyy hh:mm"

    End With

        Next colNum

    End Sub

    Image

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments