CODEWITHSUNDEEP
excelvba
bd528
bd528

Reputation: 886

Issue converting dd.mm.yyyy to UK date format using VBA

I have dates in the following format :-

01.01.2019
01.02.2019
01.03.2019
14.03.2019

and I would like these in UK date format of dd/mm/yyyy, using VBA conversion.

I attempted to use a REPLACE, changing "." to "/", but this outputs the dates as :-

01/01/2019
02/01/2019
03/01/2019
14/03/2019

Is there a way to overcome this?

Upvotes: 0

Views: 124

Answers (2)

JvdV
JvdV

Reputation: 75870

What does this do for you? I'm curious if it would return dd/mm/yyyy for you:

With ActiveSheet.UsedRange.Columns("A").Cells
    .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlDMYFormat)
    .NumberFormat = "dd/mm/yyyy"
End With

Upvotes: 1

Error 1004
Error 1004

Reputation: 8220

The below code loop column A & convert the date in column B. Try:

Option Explicit

Sub test()

    Dim Year As String, Month As String, Day As String, WholeString As String
    Dim i As Long, Lastrow As Long

    With ThisWorkbook.Worksheets("Sheet1")

        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 1 To Lastrow

            WholeString = .Range("A" & i).Value
            Year = Right(WholeString, 4)
            Month = Mid(WholeString, 4, 2)
            Day = Left(WholeString, 2)

            With .Range("B" & i)
                .Value = CDate(Day & "/" & Month & "/" & Year)
                .NumberFormat = "dd/mm/yyyy"
            End With

        Next i

    End With

End Sub

Upvotes: 4

Related Questions