VBA Convert date to MDY

Alisya

Well-known Member
Joined
Nov 27, 2008
Messages
532
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
1Date
26-Jul-11
319/05/2014
419/05/2014
55-Oct-15
65-Oct-15
724/05/2015
824/05/2015
919/05/2013
1019/05/2013
111-Dec-38
121-Dec-38
131-Dec-38
141-Dec-38
151-Dec-38
161-Dec-38
171-Dec-38
181-Dec-39
191-Dec-39
201-Dec-39
211-Dec-39
Sheet1
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Alis, try this. If cell colour is yellow then format it "DD/MM/YY") right?

Code:
[/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
 
Upvote 0

Alisya

Well-known Member
Joined
Nov 27, 2008
Messages
532
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?

Code:
[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]
 
Upvote 0

delaneyjm

Well-known Member
Joined
Apr 22, 2009
Messages
624
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.
 
Last edited:
Upvote 0

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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).
 
Upvote 0

Alisya

Well-known Member
Joined
Nov 27, 2008
Messages
532
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.
 
Upvote 0

meldoc

Well-known Member
Joined
Jul 18, 2009
Messages
1,249
Hello Alisya

Try this macro that I recorded.

Code:
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
HIJ
1DateDate
206-Jul-1107-Jun-11
319/05/201419/05/2014
419/05/201419/05/2014
505-Oct-1510-May-15
605-Oct-1510-May-15
724/05/201524/05/2015
824/05/201524/05/2015
919/05/201319/05/2013
1019/05/201319/05/2013
1101-Dec-3812-Jan-38
1201-Dec-3812-Jan-38
1301-Dec-3812-Jan-38
1401-Dec-3812-Jan-38
1501-Dec-3812-Jan-38
1601-Dec-3812-Jan-38
1701-Dec-3812-Jan-38
1801-Dec-3912-Jan-39
1901-Dec-3912-Jan-39
2001-Dec-3912-Jan-39
2101-Dec-3912-Jan-39
Sheet1
Excel 2010
 
Upvote 0

Alisya

Well-known Member
Joined
Nov 27, 2008
Messages
532
Thanks Meldoc this works as required

Hello Alisya

Try this macro that I recorded.

Code:
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
HIJ
1DateDate
206-Jul-1107-Jun-11
319/05/201419/05/2014
419/05/201419/05/2014
505-Oct-1510-May-15
605-Oct-1510-May-15
724/05/201524/05/2015
824/05/201524/05/2015
919/05/201319/05/2013
1019/05/201319/05/2013
1101-Dec-3812-Jan-38
1201-Dec-3812-Jan-38
1301-Dec-3812-Jan-38
1401-Dec-3812-Jan-38
1501-Dec-3812-Jan-38
1601-Dec-3812-Jan-38
1701-Dec-3812-Jan-38
1801-Dec-3912-Jan-39
1901-Dec-3912-Jan-39
2001-Dec-3912-Jan-39
2101-Dec-3912-Jan-39
Sheet1
Excel 2010
 
Upvote 0

Forum statistics

Threads
1,191,580
Messages
5,987,438
Members
440,097
Latest member
Wint

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top