trying to change dd/mm/yy to mm/dd/yy

[bigdan]

Dates		Text to Columns	Text to Columns	Text to Columns		Search "/"
23/11/2018		23	11	2018		3
1/3/2019		1/1/1900	3	2019		#VALUE! <tbody> </tbody>
31/03/2019		31	3	2019		3
1/11/2018		1/1/1900	11	2018		#VALUE!
24/10/2018		24	10	2018		3

<tbody>
</tbody>

The first column above shows the dates I have. I've tried changing them from dd/mm to mm/dd but it doesn't seem to be responding properly.

As one example I tried going to Text to Columns so that I could separate it into individual parts. Didn't work out too well. I later used the front-slash as the separator but the results were weird. You can see the results in columns B-E above. I've bolded/underlined the cells not behaving properly.


Can anyone advise what's going on?

 

[Joe4]

If you use Text to Columns so that cell B2 is 23, C2 is 11, and D2 is 2018, you can convert to a date using this formula:

=DATE(D2,C2,B2)

And since it is now a date, you can apply any Date format on it that you like.

 

[Fluff]

With text to columns, Delimited > uncheck all boxes > select DMY > OK

 

[Joe4]

With text to columns, Delimited > uncheck all boxes > select DMY > OK

Forgot about that!

 

[Fluff]

So many posts here have US style dates, that when I copy the data to Xl I've got to do it, but selecting MDY instead.
So it's difficult to forget

 

[bigdan]

Thanks guys.

I think I may not have been clear. If you see the data above, the third and fifth rows have something weird going on, where they're not responding to the Text to Columns function or to the search function properly. I'm wondering what's going on and how to fix that.

 

[Joe4]

I am pretty sure it is because you have mixed data types there. If you notice some entries are right-justified and others are left-justified, that is a dead giveaway.

23/11/2018 is not a valid date if using the US format, so it is coming across as Text.
3/1/2018 IS a valid date, so it is coming across as a Date.

Valid dates are actually stored as numbers, specifically the number of days since 1/0/1900. They just have some sort of date format applied.
However, formats only affect the display, not the underlying value. So real dates do NOT have a "/" in their actual value. It is just the date format showing that.

To see this more clearly, highlight column A and change the format to "General", and you will see the text and dates as Excel sees them.

 

[Fluff]

Change the format of Col B to general, that will stop it from showing as a date.
What is your search function?