Force Short Date into UK Format DD-MM-YYYY

Question

I have code to convert a date serial into UK Formatted short dates.

I am calling the module via a C# program and I am getting an American formatted date.

Application.ScreenUpdating = False

Dim x As Workbook
Set x = ActiveWorkbook

Dim WB As Workbook
Workbooks.Open "\\Csdatg04\psproject\Robot\Project Preload\Transactions\Robot WIP\Transactions.csv"
Set WB = ActiveWorkbook
WB.Worksheets(1).Range("J:J").EntireColumn.NumberFormat = "DD/MM/YYYY"
Application.DisplayAlerts = False
x.Save
WB.Save
WB.Close  

How can I force the format to UK dates or alternatively check the dates are formatted in the UK format and, if not, convert the outcome from American to UK?

Answer 1

Here's one way to deal with fixing dates (in ColA here)

Sub ImportAndFixDates()

    Dim wb As Excel.Workbook, c As Range, v, arr

    Set wb = Workbooks.Open("C:\Users\UserHere\Desktop\tmp.csv")

    With wb.Sheets(1)
        For Each c In .Range(.Range("A2"), .Cells(.Rows.Count, 1).End(xlUp)).Cells
            v = c.Text
            If v Like "#*/#*/#*" Then Then
                c.NumberFormat = "d/m/yyyy"
                arr = Split(v, "/")
                c.Value = Join(Array(arr(1), arr(0), arr(2)), "/")
            End If
        Next c
    End With

End Sub