Date Format Correction

[hblbs]

Hello all,

I am having trouble with dates in Column C which have been formatted incorrectly after a macro was run. All dates should be dd/mm/yyyy format.

The problem occurs if the date and month in the cell are below 12 than the date is re-arranged to mm/dd/yyyy instead. So for example 08/10/2011 is changed to 10/08/2011. It seem this can only be changed manually and not using cell format.

I was wondering if there is a formula to rearrange this without affecting cells that correctly formatted. I have thousands of rows to go through so anything to speed this up would be appreciated.

Thanks in advance.

 

[Andrew Poulsom]

You can try Data|Text To Columns checking Date MDY at Step 3. If you post your macro someone may be able to correct it for you.

 

[Jonmo1]

Try adjusting your macro to use Cdate

Cdate(yourdate)

That should preserve the UK date format.

 

[hblbs]

Thanks for the reply, I have tried text to column but this does not change it. Below is the macro that I am using.

Sub SplitTexttoColumn1()
    Dim cell        As Range
    Dim iPos    As Long
    
    Sheet2.Cells.ClearContents
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    With Sheet1
        With .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
            For Each cell In .Cells
                cell.Value = WorksheetFunction.Substitute(cell.Value, "|", "�", 200)
            Next cell
            .Copy Sheet2.Range("A1")
            For Each cell In .Cells
                iPos = InStr(cell.Value, "�")
                If iPos Then cell.Value = Left(cell.Value, iPos - 1)
            Next cell
                .TextToColumns Other:=True, OtherChar:="|", ConsecutiveDelimiter:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        2), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1))
            With Sheet2.Range(.Address)
                For Each cell In .Cells
                    iPos = InStr(cell.Value, "�")
                    If iPos Then
                        cell.Value = Mid(cell.Value, iPos + 1)
                    Else
                        cell.ClearContents
                    End If
                Next cell
                .TextToColumns Other:=True, OtherChar:="|", ConsecutiveDelimiter:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        2), Array(6, 1), Array(7, 1))
                 End With
        End With
        .Columns.AutoFit
        Sheet2.Columns.AutoFit
    End With
End Sub

Thanks

 

[hblbs]

Hello Jonmo1

Not sure how to use Cdate(yourdate), the macro I use is in my previous post, feel free to ammend or add as necessary as I am not sure how it works apart from splitting text over 2 sheets.

Thanks

 

[Andrew Poulsom]

Which column contains the dates?

 

[hblbs]

The date column is H, but there are are a about 40 different columns which have dates spread over 2 sheets and over 200 columns.

 

[Andrew Poulsom]

Have you tried setting the column type to xlDMYFormat (4) instead of General (1)?