Mixed Date Formats in the same Column

Status
Not open for further replies.

paulfitz320

Board Regular
Joined
Jan 6, 2007
Messages
122
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
You can select column C, right click > format cell > Date > then choose the format that you want.
 
Upvote 0
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.
 
Upvote 0
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"?
 
Upvote 0
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!
 
Last edited:
Upvote 0
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?
 
Last edited:
Upvote 0
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):


Code:
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
 
Upvote 0
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?
 
Upvote 0
:eek: 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?
 
Upvote 0
Akuini your macro did the trick!
Thanks very much for all the replies. Much appreciated!
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,585
Messages
6,114,520
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top