How to Format Dates in Descending Order in Microsoft Excel

Excel 2013's sorting feature intelligently interprets data based on cell formatting, so in most cases, when you sort a column of dates, Excel correctly sorts by chronological order rather than alphabetically. If you're dealing with a data set wherein your cells are structured as plain text, however, you first need to convert the text to a date format that Excel can understand.

Sort Dates Chronologically

Sort buttons on the Data tab.
credit: Image courtesy of Microsoft

Select the column (or part of a column) containing the dates you want to sort, open the Data tab and click either the AZ or the ZA sorting button. When working with dates, these buttons change from sorting alphabetically to sorting chronologically. The AZ button sorts from oldest to newest, while ZA sorts from newest to oldest.

Reformat Text as Dates

If you try to sort dates, but Excel sorts alphabetically instead, you have your cells set to text formatting. Changing to date formatting won't solve the problem, however, unless you retype each of your dates. If you have a long list, a simpler solution is to use the DATEVALUE function.

Step

Select an empty cell in the same row as your first date. Start a formula with =DATEVALUE( and then click the first date in your list to insert its cell location. Press Enter to complete the formula.

Start the formula.
credit: Image courtesy of Microsoft
Fill the cells.
credit: Image courtesy of Microsoft

Step

Select the cell with the finished formula and drag the fill handle (the small square in the lower-right corner of the cell) downward to cover as many rows as you have dates.

Format serial numbers into Short Dates.
credit: Image courtesy of Microsoft

Step

Select all of the cells with date serial numbers, open the Home tab and open the drop-down menu in the Number section. Pick either Short Date or Long Date to reformat the cells as dates. Sorting these cells will now work as expected.