VBA - Number to Date Change

[fpmsi]

I have a spreadsheet with a number field that needs to be changed to a date format.

Field values:



2092011 Should Be: 09/2/2011
20092011 Should Be: 09/20/2011
1102011 10/1/2011
3092011
10092011
16092011
21092011
25092011

So basically the number formats are either "DDMMYYYY" and "DMMYYY" depending on if the date is a single digit or multiple.

Any help would be appreciated.

Thanks
Ryan.

[fpmsi]

Basically I need to alter that field depending on the number of characters.

If the data is 2092011 (7 characters) Then
=(MID(M2,2,2) & "/" & "0" &LEFT(M2,1) & "/" & RIGHT(M2,4)) = 09/02/2011

and

If the data is 20092011 (8 characters) Then
=(MID(M3,3,2) & "/" & LEFT(M3,2) & "/" & RIGHT(M3,4)) = 09/20/2011

But not sure how to do this within vba to correct the whole column.

[fpmsi]

Wasnt able to figure out how to do this in vba but simply creating a new column and copy paste special using values i was able to correct my issue

Code used:
=IF(LEN(M2)=7,(MID(M2,2,2)&"/"&"0"&LEFT(M2,1)&"/"&RIGHT(M2,4)),(MID(M2,3,2)&"/"&LEFT(M2,2)&"/"&RIGHT(M2,4)))

Thanks

[RuralGuy]

There is always more to learn when you work out the problem yourself, and it did not take very long in your case; less that 1/2 hour. Excellent!