Mm/dd/yy AND dd/mm/yy AND mm-dd-yy AND dd-mm-yy in one column

Viktoria - Jan 20, 2015 at 05:35 PM
 RayH - Jan 23, 2015 at 12:20 PM

I have the problem of having all the 4 date formats in the very same column:

I would need all of them to be dd-mm-yyyy. Now I'm doing the regular find-replace method. Could you help if there's an easier way of converting all of the formats to dd-mm-yyyy date format?


1 response

ArashMan Posts 38 Registration date Monday December 15, 2014 Status Member Last seen February 18, 2015 1
Jan 21, 2015 at 01:16 AM
Dear Viktoria
first yous should select them all
then go to format cells. after that in first tab which is number select Date. and then select which type do u want,
i hope that is what you are looking for.

Thanks for your quick reply. I tried what you suggested, but not all the formats change, some stay the same..
How do you know which one is which?

dd-mm-yyyy or mm-dd-yyyy

01/06/2015 could be either. Is it January 6th or June 1st?

If the can be separated into types then a pretty simple string manipulation routine would be able to set them to the same.
Viktoria > RayH
Jan 23, 2015 at 11:02 AM
Well, I know it because I have the report only for 1 month. What do you mean by string manipulation? The thing is that I cannot really separate them into types because I should keep the order since it's a big report.
RayH > Viktoria
Jan 23, 2015 at 12:20 PM
Take this date as an example:

01/06/2015 (dd/mm/yyyy)

And you need to change it to be 06/01/2015 (mm/dd/yyyy)
Then this will switch the dd and mm parts

=MID(A1,4,3) & MID(A1,1,3) & MID(A1,7,4)

This assumes that each part being switched has two digits, 01 and not 1 because then it wont work.