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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,936
Office Version
  1. 365
Platform
  1. 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
6,975
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
6,975
Office Version
  1. 365
Platform
  1. 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,936
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
8,216
Office Version
  1. 365
Platform
  1. 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
6,975
Office Version
  1. 365
Platform
  1. 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.

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

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
Top