Looking for Formula Or Macro To Fix Date Formatting Problem

[Excelmancan]

Hi Everyone,

Here is my issue. I have a large dataset that I download from an outside source from time to time. All is good except the dates. Is there a way that a formula or a macro can be created that would convert the format that is seen below in the 1st 6 example dates that we be returned in the format as seen with the balance without affecting the "good dates".

2012-01-17
2012-03-17
2012-05-17
2012-08-17
2012-09-17
2012-12-17
12/14/17
12/17/17
12/19/17
12/23/17
12/27/17
12/28/17
12/31/17

<colgroup><col></colgroup><tbody>
</tbody>

Thanks.

 

[Excelmancan]

Or better yet, when they were uploaded - they did not upload properly. Part of it came in as 2010 instead of 2017. The first 3 dates should actually be Oct 6th, 7th and 10th in 2017 respectively. I guess the question now is, what kind of formula can be made to convert these to the proper date in 2017 as mentioned.

 

[mse330]

The below adjusted formula should work but honestly I would really be concerned about the data ... Perhaps you can fix the data source

=IF(--ISNUMBER(A1)=0,DATE(MID(A1,7,2)+100,MID(A1,1,2),MID(A1,4,2)),DATE(DAY(A1)+2000,YEAR(A1)-2000,MONTH(A1)))

 

[Excelmancan]

Thanks again for your help.