Previous Topic Next Topic

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

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

I have the problem of having all the 4 date formats in the very same column:
mm/dd/yyyy
dd/mm/yyyy
mm-dd-yyyy
dd-mm-yyyy

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?

Thanks!

1 reply

Answer 1 / 1
ArashMan
Posts
41
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.
0
Viktoria
Jan 21, 2015 at 12:34 PM
Hi,

Thanks for your quick reply. I tried what you suggested, but not all the formats change, some stay the same..
0
RayH
Jan 21, 2015 at 04:00 PM
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.
0
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.
0
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.
0