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 • 468

  • Prakash R
    Prakash R 2 days ago

    Tq bro

  • Mohamed Akl
    Mohamed Akl 5 days ago

    Thank you alot , i was really having a big mess with the dates typically as this prob. you solved >>> Thank YOu :)

  • Talmiior
    Talmiior 11 days ago

    That worked perfectly for me! Thank you so much for this video!!

  • Joanna Foley
    Joanna Foley 12 days ago

    You're the Best! Like many others, I've spent at least an hour trying to find out how to fix the problem. Project for work is now complete thanks to you.

  • Dialectical Monist
    Dialectical Monist 12 days ago

    May flights of angles sing thee to thy rest.

  • Sopana Raamalingam
    Sopana Raamalingam 12 days ago

    Thanks alot for your video sir

  • itsdaj
    itsdaj 14 days ago +1

    Thank you a million times.

  • ana ruiz
    ana ruiz 17 days ago

    hi, It works for the dates but not when the numbers are formatted as text. I tried everything and the text to column function does not work . any suggestions?

  • Jake Smith
    Jake Smith 28 days ago

    Thank you!! Very clear instructions.

  • Credenzy International

    wow that was so perfect , thank you dear

  • Susie Erickson
    Susie Erickson Month ago +4

    This is exactly what I was looking for! Thank you so much.

  • Tucker November
    Tucker November Month ago

    Thank you! This video sorted my issue! The big secret was just to make sure none of the delimiters were selected in the Text To Column dialog box and that when you select the date there you select WHAT IT CURRENTLY IS, rather than WHAT YOU WANT IT TO BE, as I had been doing, as you change it to what you want it to be after it's properly formatted as date. Thank you again!!

  • AMTBAL IT
    AMTBAL IT Month ago

    Excellent tutorial. This is a lifesaver. Thank you mate.

  • Tayyab Ilyas
    Tayyab Ilyas Month ago

    How about if date has time with it, and its recognized as text. How to change that
    09/30/2019 16:39:47

  • MARGARET PACITTI
    MARGARET PACITTI Month ago

    Thankyou so very much, you fixed my problem! I've been trying to fix it for weeks and other videos haven't helped.

  • JM Macato
    JM Macato Month ago

    Thank you very much! This worked like a charm!

  • Juanita Aldrete
    Juanita Aldrete Month ago

    Hello, Thank you so very much for this helpful video. This has helped me tremendously!

  • Julia Nicolson
    Julia Nicolson Month ago

    Hello, i exported my call log, but in the column with the date, it has included the time in brackets, and it won't sort my dates correctly - can you help?

  • Vivian Tang
    Vivian Tang Month ago

    YOU ARE A LIFESAVER, THANKS SO MUCH!!!!

  • Nina Ninasta
    Nina Ninasta Month ago

    Thanks a lot!

  • Leonard Burca
    Leonard Burca Month ago

    Awesome guide. Thanks

  • Benjamin Ehambaram
    Benjamin Ehambaram Month ago

    Thanks, great video!

  • Willem Van Wyk
    Willem Van Wyk Month ago

    Great stuff work like a charm.

  • Sonny Kanyinji
    Sonny Kanyinji Month ago

    Thank you so much!!!! It really frustrated me for more than 2 hours.

  • Jared Boyd
    Jared Boyd Month ago +1

    I know this video was submitted over three years ago, but I am hoping you are still monitoring comments on it. So I have a similar problem. I have multiple entries of the same employee ID with different dates. I need to sort by the employee ID, but I also need the dates to sort in chronological order with the employee IDs. Will this fix this issue? It was doing it, but now it is not. Let me know if this makes sense. Thanks.

    • Jared Boyd
      Jared Boyd Month ago

      OK I figured it out, I had to First sort the dates chronologically, then sort the employee id

    • Jared Boyd
      Jared Boyd Month ago

      The Tech Train any thoughts?

    • Jared Boyd
      Jared Boyd Month ago

      So I am not crazy for thinking that when column a (employee id) is sorted a-z, the dates (column M) should sort in chronological order. Correct?

    • Jared Boyd
      Jared Boyd Month ago

      The Tech Train that’s a good question. I have tried to change the dates short date formatting, I have also tried formatting as a table. I go back to try sorting a-z for employee Id and they still don’t sort correctly. I need them to so that I can measure day interval between each record, but when they don’t show in chronological order it stops my work in its tracks.

    • The Tech Train
      The Tech Train  Month ago +1

      Hello Jared, yes, I do still reply to comments on all my videos. Thank you for yours. Can I ask whether the problem with your dates is similar to what is shown in this video, as in that they don't sort correctly because of mixed formatting?

  • Gavan Plunkett
    Gavan Plunkett 2 months ago

    Thanks - that worked perfectly

  • Saydrina Ann Govender
    Saydrina Ann Govender 2 months ago

    If that didnt work, make sure your dates are dates. e.g. 30/02 or 31/09 are recognized as text.

  • krishnasamy muthusamy
    krishnasamy muthusamy 2 months ago

    Thanks a lot sir

  • Svrco Lola
    Svrco Lola 2 months ago +1

    Hi i have problem on my application for job.It's says date should be in format DD.MM.YYYY, and i put dates like that expl: 21.10.2019 and it's says following requires your attention.Can u help pleasee

    • The Tech Train
      The Tech Train  2 months ago

      I'm afraid without seeing the form I wouldn't be able to help. Just make sure you're entering the month and day the required way round. Do you have a link to the online form?

  • Fadwa Jansen
    Fadwa Jansen 2 months ago +1

    Thank you sir much appreciated :-)

  • Rebecca Morris
    Rebecca Morris 2 months ago +1

    You are AMAZING!!!!! Thank you !!

    • The Tech Train
      The Tech Train  2 months ago

      Thank you so much, I'm very glad you liked it

  • Nicolas Gentile
    Nicolas Gentile 2 months ago +1

    Thanks so much mate

  • Helping Hands
    Helping Hands 2 months ago +1

    Thankyou very much sor

  • Steve McElroy
    Steve McElroy 2 months ago +1

    Thank you! So helpful!!!

  • Arafat Hossain Abir 1410632030

    Sir, You will be remembered till my death. It took 7 minutes to understand this. Thank you so much.

    • The Tech Train
      The Tech Train  2 months ago

      Ha ha! Thank you so much, I'm very glad to have helped.

  • IT Security Specialist
    IT Security Specialist 2 months ago +1

    Thank you so much. I wonder why some older Microsoft Excel versions make this conversion automatically but some does not. Or is it because of the localization and language settings of the PC?

    • The Tech Train
      The Tech Train  2 months ago

      I think it's just Microsoft's way of making us all feel inferior and confused.

  • Maria Reena
    Maria Reena 3 months ago +1

    Thank you!!!!! This saved so much time.

  • Lloyd Christmas
    Lloyd Christmas 3 months ago +1

    Amazing. Thanks a lot.

  • Shanthi Logistics
    Shanthi Logistics 3 months ago +1

    Great!! Its working fine. I have a doubt which is that is it possible to sort rows according to date and time present in two different columns? Thanks in advance

    • The Tech Train
      The Tech Train  2 months ago

      Sorry, it's taken me a while to reply. Could you be a little more specific as to what you're needing to do?

  • Hassan Javed
    Hassan Javed 3 months ago

    Bravo !!! Great Work. Thanks

  • Robert Murdoch, A.P.
    Robert Murdoch, A.P. 3 months ago

    That worked. Thanks very much!

  • Ben And LIzzie Smith
    Ben And LIzzie Smith 3 months ago +1

    Thank-you !!!!

  • Murali John
    Murali John 3 months ago

    when i use filter its not showing the DATE colum seperately how to fix that

  • Satoshi Nakamoto
    Satoshi Nakamoto 3 months ago

    Correction : you meant the dates are in US format at 5:13 - 5:14

  • Satoshi Nakamoto
    Satoshi Nakamoto 3 months ago +1

    thanks a lot . i think i fgured it out. its the problem with the erp system that changes the date into something else.

  • James Fechtner
    James Fechtner 3 months ago +1

    Top man. Thanks! Worked a treat!

  • Ivan Pozdneev
    Ivan Pozdneev 3 months ago +1

    great decision, thanx

  • USA Tax and Accounting
    USA Tax and Accounting 3 months ago +1

    Thank you. This have been extremely helpful.

  • Deb Reneau
    Deb Reneau 3 months ago +1

    I'm still not able to sort my dates. I have a format of m/yy, the =istext says it's False. But the Year doesn't sort correctly. Any ideas?

  • Robin Spitzer
    Robin Spitzer 3 months ago +2

    Thank you! I can't tell you how many hours over several years this has haunted me....

  • Chaitu Bhai
    Chaitu Bhai 4 months ago +1

    lets say i have an excel file having date values :
    2/1/1970
    7/1/1970
    8/1/1970
    10/1/1970
    11/1/1970
    //this is in date format mm/dd/yyyy

    15-01-1970
    15-01-1970
    16-01-1970
    //but having this date in text format,but i want it in date format mm/dd/yyyy
    .

    i have tried using method from this video reference but didnt work,any suggestion??

    • The Tech Train
      The Tech Train  2 months ago

      Can you not convert text to columns and swap the dates round? I'm not sure what the issue is.

  • lucy lemann
    lucy lemann 4 months ago +1

    Thankyou

  • Alswell Wellington
    Alswell Wellington 4 months ago +1

    Thank for the info. please how do i change a long sheet of date in dd/mm/yy format to mm/dd/yy format

    • The Tech Train
      The Tech Train  2 months ago

      You'll just need to use the Convert Text to Columns feature, and then swap the two columns over.

  • Emma Karlsson
    Emma Karlsson 4 months ago

    Any idea what to do when the data source is a number (istext = False) but it shows as text in my Pivot table? Format is set to date and it's correctly showing as the month "jan", "may" based on the data. Still months don't want to be sorted right. It's not A-Z order either.

    • The Tech Train
      The Tech Train  2 months ago

      I think it would be easier for me to see the file in order to help you

  • Jenny Chong
    Jenny Chong 4 months ago

    THANK YOU THANK YOU THANK YOU!

  • Darshana Hashendra
    Darshana Hashendra 4 months ago

    Very important lesson . you save my day. Thank you. this is a solution for lots of problems not only the sorting issue. this vBLog helps to solve another problem I had

  • Alan Fallow
    Alan Fallow 5 months ago

    I wonder if anyone has a solution to my prob. I have a list of money transactions. But instead of 2 columns with in and out they are all in one and have money in. And money out. Is it possible to separate them into separate columns without doing it one by one. Thanks for any help.

  • Thomas Robinson
    Thomas Robinson 5 months ago

    Thank you for solving this issue!!

  • Audre Hoyt
    Audre Hoyt 5 months ago

    Thank you!!! I was so frustrated.

  • Joel Babb
    Joel Babb 5 months ago

    very useful! thanks.