How to Format Dates in Descending Order in Microsoft Excel
Putting dates in the correct order usually takes a single click. If Excel sorts incorrectly, however, you'll need the DATEVALUE function.
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
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.
Excel can't handle dates prior to January 1, 1900. If you need to sort older dates, the easiest solution is to cut and paste cells by hand. If you're an expert user, however, it is possible to work with older dates using Visual Basic for Applications.
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.
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.
At this point, the formula result looks like a random number. Excel uses these date serial numbers to work with dates. Each number represents the number of days since January 1, 1900.
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.
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.
- For other date formats, click More Number Formats at the bottom of the drop-down menu.
- To replace your original column of dates with the fixed column, copy the fixed column, right-click the original column and press S and then A to select Paste Values & Number Formatting.