Viewer George sends in a column of dates that refuses to be sorted. George says that he already tried converting the text dates to dates using the text to columns trick. In Episode 489, well take a look at two methods to tell if your dates are really dates and how to convert them to real dates.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question was send in by George.
George send in this column of dates.
He said hey he started out as text date.
I already went through and did the text to columns trick but no matter what I do when I try and sort these they do not sort correctly.
Now, this date format happens to be day month year.
So, this is the 13th of September 2006.
The first thing I did when I went up and I looked in the formula bar, it seems to look like a real date but I had a suspicion that the text to columns for some reason didn't work.
I have two ways to prove that, if I go to Tools, Options and on the Transition tab turn on Transition navigation keys, this forces Excel to show text values with a leading character.
In this case the carot indicating that it was a centered text value and still is a center text value.
The other way to go, when we do control grave, it puts us in show formulas mode and instead of showing us the serial number of the date.
It's showing us the actual date which tells me that it's text as well, I'll use control grave again.
So, my solution here is I'm going to make a copy of this column, copy column C and copy it over to D.
Select the column again and use Data, Text to Columns, doesn't matter which I choose in the first step. I'm going to click next next put in Step 3 I want to say this column is a Date format and is a day month year when I click finish.
You'll see the Excel actually converts to real dates if I use the control grave trick again, you'll see that the original dates the text dates still show up as dates, but the new dates show up as a serial number and that tells us that we really have dates there.
Now, when I sort this click the A to Z button, it's sure enough it puts august first and goes down to October at the end.
So, little bit frustrating if you don't have Transition Navigation turned on, it's hard to look at a date and tell whether it's a date or not Tools, Options, Transition click that checkbox you can actually see the leading character or use control grave.
Grave is a backwards accent key, that on US keyboards is right underneath the Tilde below the F1 key to see whether or not Excel is really storing those dates as true dates, or if there still is text.
Thanks to George for sending in that question.
If you have a question, please feel free to send it in.
Send it to bill@mrexcel.com we'll get to you on a future podcast.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question was send in by George.
George send in this column of dates.
He said hey he started out as text date.
I already went through and did the text to columns trick but no matter what I do when I try and sort these they do not sort correctly.
Now, this date format happens to be day month year.
So, this is the 13th of September 2006.
The first thing I did when I went up and I looked in the formula bar, it seems to look like a real date but I had a suspicion that the text to columns for some reason didn't work.
I have two ways to prove that, if I go to Tools, Options and on the Transition tab turn on Transition navigation keys, this forces Excel to show text values with a leading character.
In this case the carot indicating that it was a centered text value and still is a center text value.
The other way to go, when we do control grave, it puts us in show formulas mode and instead of showing us the serial number of the date.
It's showing us the actual date which tells me that it's text as well, I'll use control grave again.
So, my solution here is I'm going to make a copy of this column, copy column C and copy it over to D.
Select the column again and use Data, Text to Columns, doesn't matter which I choose in the first step. I'm going to click next next put in Step 3 I want to say this column is a Date format and is a day month year when I click finish.
You'll see the Excel actually converts to real dates if I use the control grave trick again, you'll see that the original dates the text dates still show up as dates, but the new dates show up as a serial number and that tells us that we really have dates there.
Now, when I sort this click the A to Z button, it's sure enough it puts august first and goes down to October at the end.
So, little bit frustrating if you don't have Transition Navigation turned on, it's hard to look at a date and tell whether it's a date or not Tools, Options, Transition click that checkbox you can actually see the leading character or use control grave.
Grave is a backwards accent key, that on US keyboards is right underneath the Tilde below the F1 key to see whether or not Excel is really storing those dates as true dates, or if there still is text.
Thanks to George for sending in that question.
If you have a question, please feel free to send it in.
Send it to bill@mrexcel.com we'll get to you on a future podcast.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
