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're welcome, glad to help, & thanks for the feedback.

 

[AfuaImade]

Hi Akuini

Thank you for responding to this query.

I tried using this micro but it doesn't seem to be working for me

Please can you help as I'm having the same issue.

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

 

[Akuini]

Hi, AfuaImade
The OP's data problem is like this:
If the first 2 numbers are less than 13 then it means the date is wrong (meaning the days & months have been swapped).

Could you explain your data problem?
Could you explain 'doesn't seem to be working'? Did the code produce a wrong result?
And could you post some data as table?

 

[AfuaImade]

Hello Akuini,

Thanks for responding. Below is a sample of the data

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]7851203[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="width: 270"]Approved[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148, align: right"]12/4/2019 23:14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]7851569[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="width: 270"]Approved[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148, align: right"]12/4/2019 23:22[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]1243752[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="width: 270"]Approved by Financial Institution[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="width: 148"]13/04/2019 0:10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]7852253[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="width: 270"]Approved by Financial Institution[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="width: 148"]13/04/2019 0:30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]1657054[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="width: 270"]Approved[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148, align: right"]12/5/2019 23:38[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]7947320[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="width: 270"]Kindly enter the OTP sent to 234813***8577[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148, align: right"]12/5/2019 23:40[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]5347509[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="width: 270"]Approved[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="width: 148"]13/05/2019 0:12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]5268985[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="width: 270"]Approved[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="width: 148"]13/05/2019 0:49[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]1066630[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="width: 270"]The bank is currently unable to process this transaction. Please try another card or bank account.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="class: xl65, width: 148, align: right"]12/6/2019 23:21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]8851513[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 270"]
<tbody>[TR]
[TD="width: 270"]Approved by Financial Institution[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 148"]
<tbody>[TR]
[TD="width: 148"]13/06/2019 0:33[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The data is specifically for month 4,5,6 (ie April,May,June) so therefore I know that even if the first 2 numbers are less than 13, it doesn't mean that the date as in the first row is not December but April.

When I ran the code, I got the error below highlighted in Yellow

If Left(x, 2) < 13 Then va(i, 1) = DateSerial(Right(x, 4), Left(x, 2), Mid(x, 4, 2))

Hi, AfuaImade
The OP's data problem is like this:
If the first 2 numbers are less than 13 then it means the date is wrong (meaning the days & months have been swapped).

Could you explain your data problem?
Could you explain 'doesn't seem to be working'? Did the code produce a wrong result?
And could you post some data as table?

 

[Peter_SSs]

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

I have copied you last post to your other thread on this topic. Please continue there as I have closed this thread