Different date formats in Excel
You probably have a BIG issue with your file!
Probably, Excel is recognizing dates as "dd/mm/yyyy" but your data is "mm/dd/yyyy" formatted.
So, your numbers are really dates (just format, as @t.thielemans suggested). But are incorrectly parsed - month and day are switched!
Solution (assuming your dates are on A:A
column):
To convert text to date:
=DATE(RIGHT(A1;4);LEFT(A1;2);MID(A1;4;2))
To correct day/month:
=DATE(YEAR(A1);DAY(A1);MONTH(A1))
Detect whether cell has date (dates are numbers, in Excel) or text:
=ISNUMBER(A1)
Finally, all combined within one formula:
=IF(ISNUMBER(A1);DATE(YEAR(A1);DAY(A1);MONTH(A1));DATE(RIGHT(A1;4);LEFT(A1;2);MID(A1;4;2)))
Just drag last formula from first row to end of your data and then format it as you wish (see @t.thielemans solution).
Related videos on Youtube
CustomX
I'm a Network Engineer ready to help with a decent knowledge of Excel (VBA). I also enjoy gaming and a bit of web development. SOreadytohelp
Updated on July 12, 2022Comments
-
CustomX almost 2 years
I have a csv that contains a list of dates, once imported into Excel they are in 2 different formats. How can I get them all in the same format?
Example:
01/23/2012 01/26/2012 40910 41031