Excel Won't Sort Dates Correctly - The Solution!

Share
Embed
  • Published on May 5, 2016
  • This video is in response to a request from Imran who explained that although he had followed the advice in one of my earlier videos about how to sort dates in Excel, his spreadsheet wasn't working, and that however much he tried, Excel just wouldn't sort his dates by year correctly. In this video I'm using Imran's own spreadsheet to demonstrate what the problem is, and explain clearly step by step how to solve it!
  • Howto & StyleHowto & Style

Comments • 372

  • Xhevdet Bytyçi
    Xhevdet Bytyçi Day ago

    Thanks a lot, you've really helped me sort a problem

  • Sampath Kumar
    Sampath Kumar 2 days ago

    EXCELLENT EXPLANATION.

  • Pedro Ponciano
    Pedro Ponciano 2 days ago

    Thank you for the help!

  • WWEEZZYY
    WWEEZZYY 5 days ago +1

    so helpful! this issue has been a real problem for me, for a long long time. Thank you!

  • Ashley J
    Ashley J 13 days ago +1

    Hello. When I tried this the date went to "#######' signs. Anything to do to solve this?

    • The Tech Train
      The Tech Train  12 days ago

      The hashes simply mean that the column isn't wide enough to show all of the content. Just make the column a little wider.

  • Sarianne Hughes
    Sarianne Hughes 13 days ago

    Hi, I need to sort by month and not year. Can anyone help?

  • Bill Woo
    Bill Woo 17 days ago

    Here's a MUCH simpler alternative. No offense because this is a fine video with a highly competently explained solution, and it certainly works. But everybody including you (uploader) will love this (though still hate clueless Microsoft). 3 quick steps follow. These are the exact keystrokes for you smart, fast users; the dumbassed ribbon instructions will later follow.
    1. Select the cells with the dates to sort (or the column if appropriate), just like the video explained.
    2. Do a find/replace (keyboard shortcut for the uberproductive: alt-E, E) then type a slash (/) in BOTH the find and replace blocks.
    3. Type alt-A to "replace all". You're done.
    Now when you sort it will work just like the video solution. There's an odd technical explanation why it works, but unless you're curious, that's all you'll need. About 5 keystrokes (and if you're also good with the tab key, you won't even need to touch or even have a mouse or trackball).
    Remember, you're technically modifying the original spreadsheet, if that matters, so think about that before hitting "Save". (It probably doesn't matter to you, because just the act of sorting changes it too :) )
    If you're one who prefers clicking instead of using keyboard shortcuts, for the catastrophically idiotic "ribbon", you'll need to hunt down Find and Replace. It MAY be under Find and Select way to the right under the Home ribbon, and then under Replace on a dropdown from that (but you may have to hunt further. Microsoft LOVES to ineptly reorganize names and locations of buttons on new releases, in order to reduce user productivity). You'll know you've found the right place if the popup that appears is the same as if you went Alt-E then E. Next put in the slash (/) in both boxes, and click replace all. Then you can sort and it works correctly.
    Again, the video solution DOES work; this is just a lot quicker and IMO far easier to remember: "change slash to slash" and dates will sort correctly :)

    • Bill Woo
      Bill Woo 17 days ago

      I think everyone will LOVE this solution if they don't mind reading my slightly long post. Because it's long, though, RU-clip will move it way down in the comment section below all other comments. If you like this, "like" this comment so it bubbles up to the top. Or if YOU like it, Mr. The Tech Train uploader, "pin" the comment because I think it will reduce pain for a lot of people. And THANK YOU for this video. It's extremely well done.

  • Joe Hargrave
    Joe Hargrave 20 days ago +1

    THANK YOU!

  • Oksana Gerasymets
    Oksana Gerasymets 22 days ago

    It is helpful, but look what happens to the dates if you create a pivot table and try grouping by date. This is the problem I am still unable to resolve and the solution offered in this video (a good solution, really) does not work either. Pivot tables treat the dates as dates as long as you don't try to group by date. If you do, Excel switches from "date" format to "text" format. I tried "=istext()" and it confirms it. It's extremely irritating. Excel seems to have a mind of its own when it comes to cell formatting... I tried a solution offered by someone on a forum (I forgot where I found it). Here it is. At the very first step of creating a pivot table, tick "Add this data to the Data Model". For some reason, it fixes the problem of sorting dates. Unfortunately it makes grouping by date impossible. And I need both.

  • Susan Burns
    Susan Burns 25 days ago +1

    Thanks so much for this video. It was extremely helpful.

  • Frank Reactions
    Frank Reactions Month ago +1

    Thank you SO much!

  • RICHARD SUN
    RICHARD SUN Month ago

    I am having this problem where if the alphabets are spaced out at the start they don't seem to get aligned alphabetically.

    • The Tech Train
      The Tech Train  Month ago

      Hello Richard, could you provide a little more information please?

  • maysa sabra
    maysa sabra Month ago

    Thank you. but in my excel still not working :(

    • The Tech Train
      The Tech Train  Month ago

      I'm sorry to hear that. Perhaps you could elaborate a little?

  • Alex Campili
    Alex Campili Month ago

    Thanks for the video, I am wondering if there is a way to exclude a column from being sorted? Let's say I want to sort the DOB and name column s but have a 3rd which I want to maintain the order of. Cheers mate

    • The Tech Train
      The Tech Train  Month ago

      Hello Alex, you're absolutely right. To sort part of a table but leave other columns intact just highlight the columns to be sorted. Either of the sort methods will work then without affecting any unsorted columns.

    • Alex Campili
      Alex Campili Month ago

      I am not at a computer ATM, on my phone. I assume I would highlight just the 2 columns and select the sort button then sort by date?

  • Josephat John
    Josephat John Month ago

    THANK YOU SO MUCH SIR EXACTLY YOU KNEW WHERE I WAS STACKED

  • Rk Matre
    Rk Matre Month ago +1

    Sir very nice video

    • The Tech Train
      The Tech Train  Month ago +1

      I'm so glad you think so. Do make sure you're subscribed as there's much more to come!

    • Rk Matre
      Rk Matre Month ago +1

      This video is a helpful and excellent

    • The Tech Train
      The Tech Train  Month ago +1

      I'm very glad you liked it!

  • Sophie Xu
    Sophie Xu Month ago

    It helped a lot! Thanks!

  • Ralton Jacobs
    Ralton Jacobs Month ago

    You are a ROCKSTAR you champ you. Thank you so much for sharing this... It really helped

    • The Tech Train
      The Tech Train  Month ago

      Thank you Ralton, I'm so glad it was able to help you!

  • ram sahu
    ram sahu Month ago

    Awesome, Thank yo for making this video.It is very helpful for us.

  • Aseem Jain
    Aseem Jain Month ago

    Excellent.. Thank you so much.. had been struggling with this since long... Great video.. thank you again !!

  • Edwin Engels
    Edwin Engels Month ago

    Thanks you for your guidance This helped me sorting my dates as the column filter did not work as there was no grouping on Year, Month, Day. After the conversion the filter grouping worked great. Super helpful!

  • Sonam Dorji
    Sonam Dorji Month ago

    Frens when we type the date we should be typing it in MM/DD/Year or else we can also change it in DD/MM/Year if we have a habit of writing the dd first

    • The Tech Train
      The Tech Train  Month ago

      In the UK we type it as DD/MM/YY, but in many other countries the DD and MM are reversed. Excel provides multiple options.

  • Sonam Dorji
    Sonam Dorji Month ago

    Thanks

  • Maísa Gonçalves Pereira

    Thank you for the video! But I still have the problem, in the same file, every time I open the file in MAC and then in Windows. Is there a fix for that?

    • The Tech Train
      The Tech Train  Month ago

      I'm afraid I don't use Macs, and so I'm not really able to help you with this one. Hopefully someone else on here might be able to suggest a solution?

  • prashant00744
    prashant00744 2 months ago

    Nice 1

  • sabasNL
    sabasNL 2 months ago

    Thank you so much!

  • Diane Helmers
    Diane Helmers 2 months ago

    Thank you. This video has been very helpful

  • happy shopper
    happy shopper 2 months ago

    I sort of got the same problem but it is my surnames on my table will not go from A to Z. any ideas how to sort this? only one name refuse to be sorted and it is right at the top of the table after i have sorted the others (which are alll WELL behaved!!) help me !! thanks

    • The Tech Train
      The Tech Train  Month ago

      I'm so sorry - I really got so far behind with replying to comments. I'm caught up now, thank goodness. Hopefully I'll be a bit prompter next time!

    • happy shopper
      happy shopper Month ago +1

      +The Tech Train ha ha, LOL i really forgot what i asked you about!! never mind. thanks all the same.

    • The Tech Train
      The Tech Train  Month ago

      I'm sorry it's taken me so long to reply to you. If you're still needing help perhaps you could email the file to me and I'll take a look? It may be that that cell has been formatted differently, or maybe there is a leading space or other hidden character?

  • Jon Plackett
    Jon Plackett 2 months ago

    Really useful, easy solution. Was driving me crazy before watching this!

  • Marcus Tan
    Marcus Tan 2 months ago

    Thank you so much for sharing this!

    • The Tech Train
      The Tech Train  Month ago

      You're very welcome, I'm glad you found it useful.

  • Harshil Kanakia
    Harshil Kanakia 2 months ago

    thank you

  • Harish Kumar
    Harish Kumar 2 months ago

    How can we choose only month end date data for historical data.
    Ex: 03/31/2018 this is month end its fine. But if the data for April is 04/25/2018 and for May 05/29/2018 and for june if it is again we have month end data (6/30/2018). Then how can we pick month end data for all months without changing any dates.

    • The Tech Train
      The Tech Train  Month ago

      I am so sorry, I have read that three times and I'm still not entirely sure I understand the problem?

  • Manpreet Bindra
    Manpreet Bindra 2 months ago

    🙏

  • Snakedoc
    Snakedoc 2 months ago

    This behavior messed up my Powershell script so much. The F is this :-\
    Anyway, thank you very much.

  • Jake Anderson
    Jake Anderson 2 months ago

    THANK YOU! This was driving me crazy!

  • 3phemer
    3phemer 2 months ago

    That was very helpful.

    Thank you!

  • Cameron Cox
    Cameron Cox 3 months ago

    This was exactly what I needed! Thank you!

  • Rita Patel
    Rita Patel 3 months ago

    Thanks very helpful

  • Philbow55
    Philbow55 3 months ago

    You just saved me SO MUCH TIME!!!! THank you!

  • Alex Ottens
    Alex Ottens 3 months ago

    lifesaver

  • jermainedan01
    jermainedan01 3 months ago

    I was stuck until viewing this video. Thanks alot young man.

    • The Tech Train
      The Tech Train  Month ago

      You're very welcome! (And thanks for the 'young'! I'm 46...)

  • Lauren Lilly Goosen
    Lauren Lilly Goosen 3 months ago

    THANK YOU!!!

  • Gaurav Garg
    Gaurav Garg 3 months ago

    How can I sort data in which dates are written in different formats like
    12/01/2018. 5-jan-2017. ,. 1/15/18.

    • The Tech Train
      The Tech Train  Month ago

      You'd need to have them copied across to a second column and all formatted the same way first.

  • Ambooj Suman
    Ambooj Suman 3 months ago

    Problem not solved

  • Michelle Choy
    Michelle Choy 4 months ago

    thank you so much! This really helped!

  • Angelic Life
    Angelic Life 4 months ago

    Very informative! Thank you very much.Problem solved:D

  • Drone NewsGuy
    Drone NewsGuy 4 months ago

    Brilliant man! Even 2 years later you're still saving the world! Thanks very much for this!

  • cilvagold
    cilvagold 4 months ago

    OMG, I've waisted more than an hour trying to figure this thing out.
    Thanks a lot man.

  • panagis rallis
    panagis rallis 4 months ago

    Thanks very much!!

  • Suzzi Suzzi
    Suzzi Suzzi 4 months ago

    I had over 700 transactions in one of my spreadsheets and guess what your brilliant......I was able to sort the info using your instructions. They were clear to understand and easy to do...Thank you for Sharing this info...Regards, Sue from Brisbane Australia.

  • Tim Phillips
    Tim Phillips 4 months ago

    LMAO, This was a great tutorial but Excel had other plans for me. Every time I try this on my spreadsheet, Excel crashes. ( I am using the most current version of Office 365). :(

    • The Tech Train
      The Tech Train  Month ago

      Oh dear - that doesn't sound good. Is it working yet?

  • Julius/Paddy
    Julius/Paddy 4 months ago

    THE TECH TRAIN, A BIG THANK YOU TO YOUR VIDEO...!!!

  • Nihad Ali
    Nihad Ali 4 months ago

    thank u very much

  • Obada Barry
    Obada Barry 4 months ago

    Thank you very much!

  • Lisa White
    Lisa White 5 months ago

    How do you sort a group of birthdays in chronological order (say, for the month of January), with the format of 1-Jan (newest to oldest)? The first 2 columns are the first and last name, then their birthday, then their age. Thanks so much for your help!

    • The Tech Train
      The Tech Train  Month ago

      I'm so sorry it's taken me such an absurd amount of time to reply. Are you still in need of help with this?

  • paula soppitt
    paula soppitt 5 months ago

    Hi.
    I have followed this very carefully but everytime I click to change it from standard to date and finish, the next time I go back it is back to standard. the dates also do not move to the right. Any ideas.

  • Thomas Dent
    Thomas Dent 5 months ago

    Brilliant my man. Thank you very much. Bill Gates, kick bricks.

  • Domi Versaix
    Domi Versaix 5 months ago

    Awesome!!! EXACTLY what I was looking for!! Thank you!

  • Frontrow Al
    Frontrow Al 5 months ago

    Ive used this video a few times now, not sure why I can never remember the process as it is simples..... thanks for this fix, it is brilliant!!!

  • Justin Vandenbunder
    Justin Vandenbunder 5 months ago

    Hi, I have an additional question. I imported some data in Excel and there seem to be two date formats mixed through each other. Some of
    them are DD-MM-YYYY and others are MM-DD-YYYY. I’ve tried different methods but can’t seem to find a solution. What would you recommend?

  • Priyanka Sonawane
    Priyanka Sonawane 5 months ago

    I spend a day tackle this problem. This video helps to solve my problem quickly. Thanks for uploading awesome solution.

  • stephanie ababio
    stephanie ababio 6 months ago

    This was a life saver

  • Amit Garg
    Amit Garg 6 months ago

    superb,thanks a lot.

  • Rajesh R P
    Rajesh R P 6 months ago

    Thank you very much It worked

  • Ying Gao
    Ying Gao 6 months ago

    Wow, it worked, so amazing, thank you so much for your video, saved lots of effort in work.

  • Arun Girjapurkar
    Arun Girjapurkar 6 months ago

    That was so useful...thanks a lot

  • Kyle Krull
    Kyle Krull 6 months ago

    I wish I would have watched this video 5 years ago. Radical. Also - great little facts in there like text defaulting to left orientation.

  • Olivia Dargan
    Olivia Dargan 6 months ago

    If i have a big sheet with dates on (my pc is set dd/mm/yyyy) it sorts correctly but, when someone else tries to sort it on their pc (yyyy/mm/dd) it wont sort correctly - please help :)

  • Richard Kiyabu
    Richard Kiyabu 6 months ago

    Thank you!

  • Red Diamond Realty
    Red Diamond Realty 7 months ago

    it works, finally got it sorted. I like the =istext check- learn something new. I just don't like the new way it formats my dates. What I typically prefer to use is not an available format and makes my work messy. Why won't Microsoft wise-up and stop changing things!

  • Anne Greyling
    Anne Greyling 7 months ago

    Great solution - thanks a million Tech Train :)

  • Florian Desneux
    Florian Desneux 7 months ago

    Awesome! Thank you

  • Eric Whippy
    Eric Whippy 7 months ago

    Awesome...it works...Thank you...#lifesaver.

  • John Erwin de Guzman
    John Erwin de Guzman 7 months ago

    Spot on!

  • Fund Singapore
    Fund Singapore 7 months ago

    This was super. Thanks!

  • DarkEternity1017
    DarkEternity1017 7 months ago

    I need help. Some of my customers are repeats, and as such have multiple dates. The column is supposed to be sorted from oldest to newest, with oldest starting at the top/first. However, Excel 2016 seems to get thrown off when I put in multiple dates, and as such puts them in random spots instead of the order I want. How can I fix this? Please and thank you.

  • tye9
    tye9 7 months ago +1

    This is the best video in the history of video's. Thank you very much for making my day 1000x easier

  • Ben M.
    Ben M. 7 months ago

    Saved me about four days' work on my master's thesis. Thank you!

  • James Parker
    James Parker 7 months ago

    Helpful video, but overly long. This should be a 2 minute video. Took you 2 minutes just to tell the warm-up story.

  • James Pyburn
    James Pyburn 7 months ago

    Thank you I spent hours trying to find out what was wrong and the true/false check and
    your solution worked. XL said dates but they were not recognised. I had 400 dates from a company accounts program that I wanted in XL. All now sorted!

  • Life N Food
    Life N Food 7 months ago

    Thank you that helped

  • Dave
    Dave 7 months ago

    I am using conditional formatting to warn users of expiry dates, however if a date is entered in any format other than 20/11/18 the formatting does not work. Could you show me how to ensure that when any date is entered in a specific column or cells, that no matter how it is entered, eg, 20.11.18 or 20-11-18, it will be converted to this format 20/11/18.

  • Eleanor Hogan
    Eleanor Hogan 7 months ago

    Thanks sorted my problem

  • Daniel Castro
    Daniel Castro 8 months ago

    Fantastic, thanks a lot, it worked perfectly. how annoying bug!

  • Zaw Ye Tun
    Zaw Ye Tun 8 months ago

    This works perfectly for me, thank you :)

  • Arysta Cell
    Arysta Cell 8 months ago

    Awesome video. Perfect solution. Thank you very much!

  • Suvasish Mistry
    Suvasish Mistry 8 months ago

    thanks a lot , i had also same problem. i just solved after watching this.

  • Juan Ignacio Previgliano

    very clear, thank you very much

  • Ross Johnson
    Ross Johnson 8 months ago

    I finally fixed it - this did work!

  • Ross Johnson
    Ross Johnson 8 months ago

    Unfortunately, this doesn't work... all of my fields work as dates, but still won't sort properly.

    • The Tech Train
      The Tech Train  7 months ago

      Feel free to email me and send me the file for me to have a look at for you.

  • Tim Y.
    Tim Y. 8 months ago

    My date column is mostly full, but I don't have all the fields populated because I just don't have all the data. Will this still work with a few empty rows ?

    • The Tech Train
      The Tech Train  7 months ago

      I haven't tried it, but I don't see why not. if it doesn't work correctly let me know and I'll take a look.

  • SUN BUSINESS SERVICES
    SUN BUSINESS SERVICES 9 months ago

    good info thanks a lot

  • lindsayk Lindsay
    lindsayk Lindsay 9 months ago

    Excellent!!! Was looking all over the place for a solution to the issue after downloading a CSV file. You nailed it. Thank You!!

  • louise ryalls
    louise ryalls 9 months ago

    Thank you lifesaver!

  • Stacie Perea-Medina
    Stacie Perea-Medina 9 months ago

    THANK YOU SO MUCH!!!! Who knew!!!

  • Brian Cherry
    Brian Cherry 9 months ago

    Wow - that worked perfectly, and well described! Nice job, thanks for the video!

  • Rajan Sood
    Rajan Sood 9 months ago

    VERY HELPFUL !!! THANKS

  • anshul tula
    anshul tula 9 months ago

    This was really helpful !!! This date problem was such a pain and led to lots of reworks! Thanks a lot..:)

  • hemangforce1
    hemangforce1 9 months ago

    Amazing!!! this is what I was looking for thank you so much...

  • Steven Fossil
    Steven Fossil 9 months ago

    Thank you so much for this video, had been searching a lot but finally got something which actually helped me. Thank you once again.

    • The Tech Train
      The Tech Train  7 months ago

      You're very welcome. I'm glad it worked for you.