Mixed Date Formats in the same Column

[paulfitz320]

I have a big spreadsheet, some 750000 rows.

In Column C I have dates.

Approximately half of the dates in this Column are in dd/mm/yyyy format, the other half in mm/dd/yyyy format, I think.

eg. 31/05/2012
then 01/06/2012 (appears as 06/01/2012 in the formula window)

Any help to get them ALL to dd/mm/yyyy really appreciated.

 

[Akuini]

You can select column C, right click > format cell > Date > then choose the format that you want.

 

[paulfitz320]

The dates are mixed format, I dont need to change them all, which is what will happen. I'll just end up with the same problem reversed, if I use your suggestion. Thanks.

 

[Yongle]

Oh dear
- sounds like some of the underlying values are incorrect

04/03/2012 How would you know if 4th March or 3rd April????
In principle - identify the "bad" dates and convert their underlying value to the correct one by switching month for day

BUT are you able to identify ALL values that are "bad"?

 

[paulfitz320]

That's my problem, spreadsheet is dd/mm/yyyy and thinks 04/03/2012 is okay, when I need it be 03/04/2012. Like I say approx half the dates are correct from the 13th onwards each month, but 1st to 12th each month needs to be reversed. Dreading having to change 1st to 12th each month from 2011 to 2018 manually, with 750000 rows of data!

 

[Yongle]

Doing the converting is EASY by formula
=DATE(YEAR(A2),DAY(A2),MONTH(A2))
and then copy and paste values to "fix" those values

So if you can identify the "Bad" dates, then you can do this
=IF(formula to identify bad dates,DATE(YEAR(A2),DAY(A2),MONTH(A2)),A2)

My question is can you distinguish where both month AND day are between 1 and 12?

 

[Akuini]

Is it correct to assume that if first 2 number is less than 13 then it must be the false date?
If yes then try this (in small data first):

Sub flipDate()
Dim va, x
Dim i As Long
va = Range("C2", Cells(Rows.count, "C").End(xlUp))
For i = 1 To UBound(va, 1)
    x = va(i, 1)
    If Left(x, 2) < 13 Then va(i, 1) = DateSerial(Right(x, 4), Left(x, 2), Mid(x, 4, 2))
Next
Range("C2").Resize(UBound(va, 1), 1) = va
End Sub

 

[steve the fish]

It sounds like you have imported a load of american style dates but then you say you have 31/5/2012 which isnt. Where have these dates come from?

 

[Yongle]

Reading this may not help your mood!

To me it looks like an unnecessary conversion may have been done to some of the data
- if so you may have a lot more dates wrong than you think
- and it may be impossible to establish which ones are "BAD" and which ones are "GOOD" dates by visual inspection

Try these 2 formulas in a cell
=DATE(2012,5,31) returns 31 May 2012
=DATE(2012,31,5) returns 5 July 2014

In case you are wondering
31 is (2 X 12 months = )24 + 7 (7th month is July)
and the 2 years has been added to 2012 to arrive at 2014

Only if EVERY "BAD" date is found can this problem be fixed

Is the original data is available somewhere so that you can rebuild from the source?

 

[paulfitz320]

Akuini your macro did the trick!
Thanks very much for all the replies. Much appreciated!