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!

Mm/dd/yy AND dd/mm/yy AND mm-dd-yy AND dd-mm-yy in one column
Mm/yy - Guide
Display two columns in data validation list but return only one - Guide
How to change date format from yyyymmdd to mm/dd/yy in Excel? ✓ - Excel Forum
Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names. ✓ - Excel Forum
How do i auto number a column in excel? ✓ - Excel Forum

1 response

Answer 1 / 1

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.

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..

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.

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.

Similar discussions

How do i change date format in (dd.mm.yyyy)

install graphics card driver

Change Date Format - dd/mm/yyyy HH:MM:SS to mm/dd/yyyy HH:MM:SS

enter date in dd-mm-yyyy

Date formatting in dd/mm/yyyy

Similar discussions

Subscribe To Our Newsletter!