Answer to the 5 little Tips (tests) – Part 5/5
The task: To sort the following dates in descending order. Sound silly but…
The first thing you would probably do is to go to Data tab and then click the Sort Z to A icon?
And get the following result… without even looking at it (as you trust Excel as reliable tool), believing Excel gets the job done.
Yes. Excel gets it done…
BUT are you sure the dates are sorted in the way you want? In the in-house training in my previous company, many participants really thought so…
I always remind myself: No matter how familiar I think I am with Excel, CHECK the result before sending out.
Get thing done ≠ Get thing right
The date is not sorted correctly because they are not input as numeric date. Instead, they are input (actually downloaded from SAP) as text.
You may want to see my other post: Number Stored as Text…
So how to fix the problem?
Answer: Convert the date (as text) into numeric date
There are many different ways to fix trouble date. The following screen cast shows one of them:
Here’s step-by-step instructions:
- Copy any blank cell
- Select the destination range (A4:A11 in our example)
- Paste Special (shortcut: Ctrl+Alt+V)
- Paste “Value”, with operations “Add”
- Don’t be panic when you see the five-digit numbers
- Format cell to the “Date” format you want
- Sort Z to A
- Check the result
- Yeah!!!
As simple as this. 🙂
FREE POWER BI WEBINAR
Did you manage to watch the free webinar by Mynda Treacy that I talked about last week? I just watched it this morning and was really amazed by the power of Power BI. Needless to say Mynda did a supreme job in showing the cool features of Power BI in less than one hour.
Don’t have time for an hour long webinar?
Check out Mynda’s online Power BI course, test the embedded Power BI dashboard and watch the short 12 minute video, where Mynda builds this fully interactive dashboard:
And if you decide you want to supercharge your Power BI learning journey and get up to speed fast then you can get Mynda’s online Power BI course for 20% off until Thursday, February 23rd.
Both the free webinar and Mynda’s online Power BI course are only available for a limited time, so don’t miss out.
Disclosure: I make a small commission for students who join Mynda’s course via my site, but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform your career, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.
wmfexcel 
Hello MF,
I think,I’ve caught the fastest way to sort the date list in desending order. You have to mark the list including the headline! Only then you can use the Sorting Button and get the wanted result. It seems to me, that Excel handles the first cell of a list like a headline.
I’ve tried it with a name list for sorting content in alphabetical order. If I didn’t use the headline, the first of the marked cells was used like that and not sorted.
Sabine
LikeLike
Hi Sabine,
Thanks for coming back. I have tried the way you described (in both Excel 2011 for Mac and Excel 2016)… Unfortunately, it didn’t work.
I further tested it, and I believed it is the “Sort Warning”… Once we have opted for “SORT ANYTHING THAT LOOKS LIKE NUMBER AS NUMBER”, Excel remembered it so that this setting can be reapplied, even in other workbooks.
If you go back to the “Sort” icon (not the A->Z or Z–>A), you will be prompted with the “Sort Warning”. Now if you select “Sort numbers and numbers stored as text separately”, then you will get the undesired sort result; and this kind of “sort setting” is saved.
Cheers,
MF
LikeLike
Hello MF,
indeed, it’s like you said. The “SORT ANYTHING THAT LOOKS LIKE NUMBER AS NUMBER” is one of my prefered settings. Little things -> Great impact!
You made my day. 🙂
Sabine
LikeLike
Thank you indeed. 🙂
LikeLike
To my opinion – it depends on which option you select for sorting.
Using the “Smallest to Largest (A-Z)” OR “Largest to Smallest (Z-A)” icons will not present the “Sort Error Dialog Window” BUT choosing “SORT” from the Data tab should trigger it.
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL
LikeLike
I tried that and I agree with you. Seems to be a good reason not to reply toooo much on “shortcut”. 🙂
LikeLike
Thakns, Wong.
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL
LikeLike
Hello MF,
I’ve got it even faster.
1.) Select range
2.) Format cells as Date
3.) Sort Z to A
Sabine
LikeLike
Hi Sabine,
I wonder if that works…
Did you try that to the file I put in the blogpost?
https://wmfexcel.com/2017/01/07/5-little-excel-tips-tests/
LikeLike
The answer is:”YES.” And it’s worked pretty fine with Excel 2003 Professional.
Sabine
LikeLike
That is to my surprise! I don’t have Excel 2003… i have tested on Excel 2010 and 2011 for mac , neither works.
Would you please also text the date by using ISNUMBER to verify whether it is converted to numerical date after changing the format to Date?
LikeLike
Wong,
It DOES NOT work for me either (in all older versions incl. 2003)
Suprisingly, when I tried to sort the original (text) range (in 2013 & 2003) I got a “Sort Warning” which allows me to select: “SORT ANYTHING THAT LOOKS LIKE NUMBER AS NUMBER” and after selecting it – the range was sorted as expected, while (and this is also surprising) the ISTEXT test still shows TRUE.
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL
LikeLike
Thanks Micky for testing this and share your findings. This is really interesting!
You have reminded me that “SORT ANYTHING THAT LOOKS LIKE NUMBER AS NUMBER” thing. I am sure I have seen that before but not recently. I wonder if this is an “option” hidden somewhere in advanced options…
LikeLike
Hello MF,
here are my tables:
Before sorting
Date(DD/MM/YYYY) istzahl?
01/02/2014 FALSCH
05/02/2014 FALSCH
05/12/2010 FALSCH
06/01/2013 FALSCH
06/02/2012 FALSCH
21/05/2009 FALSCH
21/05/2010 FALSCH
31/12/2008 FALSCH
After sorting:
Date(DD/MM/YYYY) istzahl?
05/02/2014 FALSCH
01/02/2014 FALSCH
06/01/2013 FALSCH
06/02/2012 FALSCH
05/12/2010 FALSCH
21/05/2010 FALSCH
21/05/2009 FALSCH
31/12/2008 FALSCH
It has taken me one click for sorting. I think, this happens, if you are using the addin called analytical function tool.
Sabine
LikeLike
Thanks Sabine for sharing your findings. This is really interesting.
Btw, you result also showed that you cannot convert a text to number by changing the format of it. They got sorted correctly for another reason.
LikeLike
Hello again,
it could be possible, that so called SmartTags are responsable for this behaviour. You find them in the Autocorrection.
Sabine
LikeLike
Hello MF,
You’ve written:
“Btw, you result also showed that you cannot convert a text to number by changing the format of it.”
I’ve just tested it with numbers with an apostroph at the beginning. I asked Excel, if this is a number and the answer was FALSE.
Then I’ve used Paste Special with multiplying 1. All text was converted to numbers and Excel gave me a TRUE.
Sabine
LikeLike
Hi Sabine
You are right. An operation may convert a number stored as text to number. I have a blogpost about Number stored as Text.
Number Stored as Text… | wmfexcel
https://wmfexcel.com/2016/05/14/number-stored-as-text/
LikeLike
I must admit I forgot where I left my “Timer” therefor, withour messuring the time, I would like to suggest ad follows:
1) Select the “Textual” Dates range (A4:A11).
2) Goto Menu: “DATA” > “TEXT TO COLUMNS” > “FINISH” (3 clicks).
3) Try to sort the renge +check if the ISTEXT Changed to FALSE.
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL
LikeLike
I must admit that I didn’t know I can go straight to “Finish” in “Text to columns”. Thank you Micky 🙂
LikeLike
Glad to be able to highlight, some “features”, from time to time.
By the way – the posting time (next to the date) displays (here): ^:^
Michael (Micky) Avidan
“Microsoft® Answers” – Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL
LikeLike
^.^ this is intentionally for a reason 😁
LikeLike