Mixed Date Formats in the same Column

Status
Not open for further replies.

paulfitz320

Board Regular
Joined
Jan 6, 2007
Messages
104
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,377
Office Version
2013
Platform
Windows
You can select column C, right click > format cell > Date > then choose the format that you want.
 

paulfitz320

Board Regular
Joined
Jan 6, 2007
Messages
104
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,705
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jan 6, 2007
Messages
104
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:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,705
Office Version
365
Platform
Windows
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:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,377
Office Version
2013
Platform
Windows
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
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,793
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
4,705
Office Version
365
Platform
Windows
: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?
 

paulfitz320

Board Regular
Joined
Jan 6, 2007
Messages
104
Akuini your macro did the trick!
Thanks very much for all the replies. Much appreciated!
 
Status
Not open for further replies.

Forum statistics

Threads
1,082,344
Messages
5,364,803
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top