VBA Convert date to MDY

[Alisya]

Hi i have a list of dates which i want to convert to MDY, these dates are highligted in yellow. I would like a macro that will automate this as this task is daily. Text to columns works if i do it manually but when i record my steps via macro and run the code it never works. So dont know what is the best method.

Excel Workbook
	J
1	Date
2	6-Jul-11
3	19/05/2014
4	19/05/2014
5	5-Oct-15
6	5-Oct-15
7	24/05/2015
8	24/05/2015
9	19/05/2013
10	19/05/2013
11	1-Dec-38
12	1-Dec-38
13	1-Dec-38
14	1-Dec-38
15	1-Dec-38
16	1-Dec-38
17	1-Dec-38
18	1-Dec-39
19	1-Dec-39
20	1-Dec-39
21	1-Dec-39
Sheet1

 

[Alisya]

bump

 

[pedie]

Alis, try this. If cell colour is yellow then format it "DD/MM/YY") right?

[/FONT]
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub i()
Dim i As Long, lr As Long
lr = Range("J" & Rows.Count).End(xlUp).Row
For i = 1 To lr
If Range("J" & i).Interior.Color = vbYellow Then
Range("J" & i).Value = Format(Range("J" & i).Value, [B][COLOR=navy]"DD/MM/YY")[/COLOR][/B]
End If
Next i
End Sub

 

[Alisya]

Pedie i only showed the cell color to differentiate between the 2 date formats, the real data will have no cell color, so i need to code to apply MDY format without including cell color

Alis, try this. If cell colour is yellow then format it "DD/MM/YY") right?

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub i()
Dim i As Long, lr As Long
lr = Range("J" & Rows.Count).End(xlUp).Row
For i = 1 To lr
If Range("J" & i).Interior.Color = vbYellow Then
Range("J" & i).Value = Format(Range("J" & i).Value, [B][COLOR=navy]"DD/MM/YY")[/COLOR][/B]
End If
Next i
End Sub
[/FONT]

 

[delaneyjm]

What is the expected year for the cells that end in 38 and 39? Typically, anything after 15 I go with the previous century and anything smaller, I go with the current.

 

[Richard Schollar]

Do you want the dates swapped around (ie from 6 Jul 11 to 7 June 11)? Because you already seem to have valid dates in those cells I presume it must be (otherwise you could simply change the numberformatting of the affected cells).

 

[Alisya]

Do you want the dates swapped around (ie from 6 Jul 11 to 7 June 11)? Because you already seem to have valid dates in those cells I presume it must be (otherwise you could simply change the numberformatting of the affected cells).

Hi Richard that is correct, i want the dates swapped, some of my dates are in Text which fine and some are in General format, those general format dates are incorrect and need to be swapped to MDY.

 

[meldoc]

Hello Alisya

Try this macro that I recorded.

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("J:J").Select
    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 4), Array(12, 1)), TrailingMinusNumbers:=True
End Sub

Excel Workbook
	H	I	J
1	Date		Date
2	06-Jul-11		07-Jun-11
3	19/05/2014		19/05/2014
4	19/05/2014		19/05/2014
5	05-Oct-15		10-May-15
6	05-Oct-15		10-May-15
7	24/05/2015		24/05/2015
8	24/05/2015		24/05/2015
9	19/05/2013		19/05/2013
10	19/05/2013		19/05/2013
11	01-Dec-38		12-Jan-38
12	01-Dec-38		12-Jan-38
13	01-Dec-38		12-Jan-38
14	01-Dec-38		12-Jan-38
15	01-Dec-38		12-Jan-38
16	01-Dec-38		12-Jan-38
17	01-Dec-38		12-Jan-38
18	01-Dec-39		12-Jan-39
19	01-Dec-39		12-Jan-39
20	01-Dec-39		12-Jan-39
21	01-Dec-39		12-Jan-39
Sheet1

Excel 2010

 

[Alisya]

Thanks Meldoc this works as required

Hello Alisya

Try this macro that I recorded.

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("J:J").Select
    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 4), Array(12, 1)), TrailingMinusNumbers:=True
End Sub

Excel Workbook
	H	I	J
1	Date		Date
2	06-Jul-11		07-Jun-11
3	19/05/2014		19/05/2014
4	19/05/2014		19/05/2014
5	05-Oct-15		10-May-15
6	05-Oct-15		10-May-15
7	24/05/2015		24/05/2015
8	24/05/2015		24/05/2015
9	19/05/2013		19/05/2013
10	19/05/2013		19/05/2013
11	01-Dec-38		12-Jan-38
12	01-Dec-38		12-Jan-38
13	01-Dec-38		12-Jan-38
14	01-Dec-38		12-Jan-38
15	01-Dec-38		12-Jan-38
16	01-Dec-38		12-Jan-38
17	01-Dec-38		12-Jan-38
18	01-Dec-39		12-Jan-39
19	01-Dec-39		12-Jan-39
20	01-Dec-39		12-Jan-39
21	01-Dec-39		12-Jan-39
Sheet1

Excel 2010