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
As simple as this. 🙂
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.
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.