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

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

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.

There is no need to "convert" US dates and/or times to UK dates and/or times. Just change the number format to

dd/mm/yyyy hh:mm

If you have dates in one column and times in another, do the following:

• Select the times.
• Copy them to the clipboard (Ctrl+C)
• Select the corresponding dates.
• Either right-click in the selection and select Paste Special... from the context menu, or click the lower half of the Paste button on the Home tab of the ribbon and select Paste Special... from the drop down menu,
• Select Add, then click OK.
• With the same cells still selected, apply the number format dd/mm/yyyy hh:mm
• If necessary, widen the column
• You can delete the separate time column now.