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

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
779
DatesText to ColumnsText to ColumnsText to ColumnsSearch "/"
23/11/2018231120183
1/3/20191/1/190032019
#VALUE!

<tbody>
</tbody>
31/03/201931320193
1/11/20181/1/1900112018 #VALUE!
24/10/2018241020183

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

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
=DATE(D2,C2,B2)
And since it is now a date, you can apply any Date format on it that you like.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,330
Office Version
  1. 365
Platform
  1. Windows
With text to columns, Delimited > uncheck all boxes > select DMY > OK
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
With text to columns, Delimited > uncheck all boxes > select DMY > OK
Forgot about that!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,330
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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 :LOL:
 

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
779
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,330
Office Version
  1. 365
Platform
  1. Windows
Change the format of Col B to general, that will stop it from showing as a date.
What is your search function?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,186
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top