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

 RayH -

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 reply

Registration date
Monday December 15, 2014
Last seen
February 18, 2015
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.
> RayH
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.
> Viktoria
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.