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.

 

[mse330]

Assuming you don't have dates that are before 1-Jan-2000 & your date is in cell A1 maybe the below formula would work

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

 

[Tetra201]

Maybe just

=DATEVALUE(A1)

 

[Excelmancan]

Perfect mse330. Yes, there can be circumstances that it is before 2000.

Assuming you don't have dates that are before 1-Jan-2000 & your date is in cell A1 maybe the below formula would work

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

 

[Excelmancan]

No DATEVALUE doesn't work in this case. Thanks.

 

[mse330]

Perfect mse330. Yes, there can be circumstances that it is before 2000.

In this case, you can just add "if condition" if you know how far back the date could get to ... For example, if you know that 1985 is the oldest date you might have then don't add the 100 in the year

 

[Excelmancan]

Very good. Thanks again !

 

[Excelmancan]

Actually I just realized that the formula does not work. Here are the results....I should have been clear with my example. Below, the first 3 dates should actually be Oct 6th, 7th and 10th in 2017 respectively.

2010-06-17	40346
2010-07-17	40376
2010-10-17	40468
10/13/17	43021
10/15/17	43023
10/17/17	43025

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

 

[PaddyD]

dates are stored as integers (with 1 = 1 Jan 1900). Just format those numbers as dates and off you go...

 

[Excelmancan]

Unfortunately, it appears that these are formatted as text.