Change format from dd.mm.yyyy to mm.dd.yyyy using macro

[divyaquamara]

Hi
I read several post but cannot find an answer to my query.
I will be greatful if the experts can help.

I have severval columns containg dates in the dd.mm.yyyy format. I want to place them in the mm.dd.yyyy format in the same column.
I want to do this using a macro.

(I have exported this data from a source and hence I think these cells are having formatting as general and not date)

The sample data is:
A B C
<TABLE style="WIDTH: 276pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=368 border=0 x:str><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=117 height=17 x:str="Start Date ">Start Date </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=116 x:str="Report Date ">Report Date </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=135>Finish Date</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>30.05.2011</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135></TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>19.08.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135>30.11.2011</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>21.01.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135></TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>20.04.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116>20.12.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135>03.06.2011</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>05.11.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116>10.11.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135>09.12.2010</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>05.11.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116>10.11.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135>09.12.2010</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>05.11.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116>11.01.2011</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135>11.03.2011</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>05.11.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116>11.01.2011</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135>11.03.2011</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>29.01.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135>10.11.2010</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>05.04.2011</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116>05.06.2011</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135></TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=117 height=40>08.03.2010</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=116></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=135>14.08.2010</TD></TR></TBODY></TABLE>

Thanking In advance.

 

[VoG]

Try selecting one column at a time, Data > Text to Columns, click Next twice, tick Date and select DMY then click Finish.

 

[kpark91]

Try

Sub convertDate()
    Dim LR As Long, i As Long, j As Long
    LR = Range("A" & Rows.count).End(xlUp).Row
 
    For i = 2 To LR
        For j = 1 To 3
            Cells(i, j).Value = Format$(DateValue(Cells(i, j).Value), "mm.dd.yyyy")
        Next j
    Next i
End Sub

OR

Try selecting one column at a time, Data > Text to Columns, click Next twice, tick Date and select DMY then click Finish.

That :D loll

 

[divyaquamara]

Hi
Thanks for the help.
But the Text to column does not give any result.

and for the code I am getting a type mismatch error at the following line:
Cells(i, j).Value = Format$(DateValue(Cells(i, j).Value), "mm.dd.yyyy")

How should I proceed?

 

[kpark91]

Hi, it's probably because your cells are in General format.
You should run this code only ONCE because this just manually changes the positions between month and date whether it be month or day

Sub convertDate()
    Dim LR As Long, i As Long, j As Long, cellDate$()
    LR = Range("A" & Rows.count).End(xlUp).Row
 
    For i = 2 To LR
        For j = 1 To 3
            cellDate = Split(Cells(i, j).Value, ".")
            If UBound(cellDate) = 2 Then Cells(i, j).Value = cellDate(1) & "." & cellDate(0) & "." & cellDate(2)
        Next j
    Next i
End Sub

 

[divyaquamara]

Thanks a ton. This does exactly what I want.