Look Who it Was Random video

Excel - Converting Text Dates to Real Dates in Excel | Sorting Issues Solved - Episode 489

965 views · Published 1 May 2009 · 2:44 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Converting Text Dates to Real Dates in Excel | Sorting Issues Solved.

Welcome back to the MrExcel netcast! In today's episode, we have a question from George about sorting dates in Excel. George sent in a column of dates that were originally in text format. He had already tried the text to columns trick, but no matter what he did, the dates would not sort correctly. Upon further investigation, we found that the date format was day month year, such as 13th of September 2006.

At first glance, the dates appeared to be in the correct format, but I had a suspicion that the text to columns function did not work properly. To confirm this, I used two methods. First, I went to Tools, Options, and on the Transition tab, I turned on Transition navigation keys. This forces Excel to show text values with a leading character, which in this case was a carot. This indicated that the dates were still in text format. The second method was to use the control grave shortcut, which puts us in show formulas mode. Instead of showing the serial number of the date, it showed the actual date, confirming that it was still in text format.

To solve this issue, I made a copy of the column and used the Data, Text to Columns function again. This time, I selected the option to convert the column to a date format and specified the day month year format. Upon clicking finish, Excel converted the dates to real dates. Using the control grave shortcut again, I could see that the original dates were still in text format, while the new dates were in a serial number format, indicating that they were now true dates.

If you're having trouble determining whether a date is in text or date format, I recommend turning on Transition navigation keys or using the control grave shortcut. These methods will help you identify any text values that may be causing issues with sorting. Thank you to George for sending in this question. If you have a question of your own, please feel free to send it to [email protected] and we may feature it on a future podcast. Thanks for tuning in and we'll see you next time for another netcast from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


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!

Table of Contents
(00:00) Sorting Dates that used to be text
(00:44) Lotus Transition Settings to show caret before date
(01:00) Show Formulas
(01:26) Text to Columns Step 3 forces a date
(02:06) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Convert text to date Excel
Copy column in Excel
Data text to columns Excel
Date format in Excel
Excel date formatting
Show formulas mode Excel
Sort A to Z in Excel
Sort dates in Excel
Text to columns
Tools options Excel
Transition navigation keys


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152391/

More from this channel