Copy and Paste can do so much…

Answer to the 5 little Tips (tests) – Part 5/5

The task: To sort the following dates in descending order.  Sound silly but…

Excel Tips - Trick #5.png

The first thing you would probably do is to go to Data tab and then click the Sort Z to A icon?

Excel Tips - Sorting Dates.PNG

And get the following result… without even looking at it (as you trust Excel as reliable tool), believing Excel gets the job done.

Excel Tips - Sorting Dates1.PNGYes. 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.

excel-tips-sort-trouble-date

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:

excel-tips-sort-trouble-date1

Here’s step-by-step instructions:

  1. Copy any blank cell
  2. Select the destination range (A4:A11 in our example)
  3. Paste Special (shortcut: Ctrl+Alt+V)
  4. Paste “Value”, with operations “Add”
  5. Don’t be panic when you see the five-digit numbers
  6. Format cell to the “Date” format you want
  7. Sort Z to A
  8. Check the result
  9. 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.

Power BI Webinar

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.

Advertisements
This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

18 Responses to Copy and Paste can do so much…

  1. Michael (Micky) Avidan says:

    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

    Like

    • MF says:

      I tried that and I agree with you. Seems to be a good reason not to reply toooo much on “shortcut”. 🙂

      Like

      • Michael (Micky) Avidan says:

        Thakns, Wong.

        Michael (Micky) Avidan
        “Microsoft® Answers” – Wiki author & Forums Moderator
        “Microsoft®” MVP – Excel (2009-2017)
        ISRAEL

        Like

  2. Sabine says:

    Hello MF,
    I’ve got it even faster.

    1.) Select range
    2.) Format cells as Date
    3.) Sort Z to A

    Sabine

    Like

    • MF says:

      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/

      Like

      • Sabine says:

        The answer is:”YES.” And it’s worked pretty fine with Excel 2003 Professional.
        Sabine

        Like

        • MF says:

          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?

          Like

          • Michael (Micky) Avidan says:

            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

            Like

          • MF says:

            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…

            Like

          • Sabine says:

            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

            Like

          • MF says:

            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.

            Like

          • Sabine says:

            Hello again,
            it could be possible, that so called SmartTags are responsable for this behaviour. You find them in the Autocorrection.
            Sabine

            Like

        • Sabine says:

          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

          Like

    • Michael (Micky) Avidan says:

      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

      Like

      • MF says:

        I must admit that I didn’t know I can go straight to “Finish” in “Text to columns”. Thank you Micky 🙂

        Like

        • Michael (Micky) Avidan says:

          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

          Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s