# Excel Question: Sorting non-standard Dates

**URL:** <https://boards.straightdope.com/t/excel-question-sorting-non-standard-dates/96570>\
**Category:** Factual Questions\
**Created:** [December 3, 2001, 4:57am UTC](https://boards.straightdope.com/t/excel-question-sorting-non-standard-dates/96570 "2001-12-03T04:57:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![richardb](https://avatars.discourse-cdn.com/v4/letter/r/96bed5/32.png) [@richardb](https://boards.straightdope.com/u/richardb)\
**Post date:** [December 3, 2001, 4:57am UTC](https://boards.straightdope.com/t/excel-question-sorting-non-standard-dates/96570/1 "2001-12-03T04:57:50Z")

</div>

I have this problem every year. After importing data into Excel, I want to sort by date, but the imported date data is not in a format that Excel recognizes. The dates are like **JAN 31 99** or **JUL 5 01** or **FEB 2 00**.

When I try to format the cells in a date format, this format of Month-one or two digit day-two digit year is not among the choices, so I cannot sort the column by dates.

In actuality, for my purposes I only need to separate each year, so in the past I have made a new column, and used the text RIGHT function to return the last two digits (the year) which I could then sort.

I suppose I could do this again, but I can’t help but wonder if there is a way to properly sort date data that is in the above format. Excel experts? Thanks in advance!

---

<div class="post-metadata">

**Author:** ![FortMarcy](https://avatars.discourse-cdn.com/v4/letter/f/9de053/32.png) [@FortMarcy](https://boards.straightdope.com/u/FortMarcy)\
**Post date:** [December 3, 2001, 5:21am UTC](https://boards.straightdope.com/t/excel-question-sorting-non-standard-dates/96570/2 "2001-12-03T05:21:11Z")

</div>

You could break the dates apart using the test-to-columns function and then construct the date with something such as DATEVALUE(B1&"-"&A1&"-"&C1)

---

<div class="post-metadata">

**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\
**Post date:** [December 3, 2001, 6:05am UTC](https://boards.straightdope.com/t/excel-question-sorting-non-standard-dates/96570/3 "2001-12-03T06:05:58Z")

</div>

**RichardB** - before the import go into control panel, regional settings, date and change the short format to **MMM dd yy**. When the Text Import Wizard kicks in, be sure to tell it that the column is a date.