VBA date change

[milesy]

HI

i run macro like below - when i do so it changes my dates from Aus (dd/mm/yy) to a US styke (mm/dd/yy) not just the layout but the actual data changes

any way to stop this??

Sub Trim()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
cell = WorksheetFunction.Trim(cell)
Next cell
End Sub

Adrian

[NickyC]

Hi
if it happens every time then try this:

Sub Trim2()
Dim celL As Range
For Each celL In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)

    celL = WorksheetFunction.Trim(celL)
    If IsDate(celL) Then
        If Day(celL) < 13 Then
            ccell = DateSerial(Year(ccell), Month(ccell), Day(ccell))
        End If
    End If
Next celL
End Sub

[milesy]

unfortunately it still does it......with this code.....BUT only if changing the numbers around makes a valid date



eg


13/02/2019 2:48:13 PM DOES NOT GET CHANGED
1/4/2019 2:38:13 PM DOES CHANGE

I should add - if i change the dates to a number first (and often have to Text to columns to make them all change ) and then run the macro (either version) there is no problem and the dates do not get changed around