How to Format Dates in Descending Order in Microsoft Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.

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.

Advertisement

Sort Dates Chronologically

Video of the Day

Image 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.

Advertisement

Video of the Day

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.

Advertisement

Step 1: Write a DATEVALUE Formula

Image Credit: Image courtesy of Microsoft

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.

Advertisement

Step 2: Fill the Remaining Cells

Image Credit: Image courtesy of Microsoft

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.

Advertisement

Advertisement

Step 3: Change Cell Formats

Image Credit: Image courtesy of Microsoft

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.

Advertisement

Advertisement

references