Good and bad dates

Google Sheets – How to Sort Dates into Chronological Order

You may already know how to use the SORT function or how to use the menus to sort a list of dates. But, these sorts don’t always work correctly with dates. Dates are notoriously tricky to work within a spreadsheet as can be seen in the linked example file.

Good and bad dates
Good and bad dates

The only hint that Sheets gives you about the validity of your dates is the alignment of the data in the cell. If it recognizes a date, it will be shown as right-aligned. Even the November 6th, 2012 date above is invalid, but it is difficult to tell because the contents fill most of the cell, hiding the fact that it is left-aligned.

In order for a date to work like a number, Google Sheets has to recognize it as valid. If Sheets does not recognize your date, it won’t be able to sort it in relation to any other dates. When Sheets does recognize a value as a date, it converts it to a serial number so that it can use it in formulas.

Check Dates for Validity

So, how do you make sure all of your dates are valid? You can do this using the DATEVALUE function. The DATEVALUE function checks a date to make sure that Sheets thinks it is valid. If the date is valid, it is returned as a serial number representation of that date. If the date is not valid, it returns a #VALUE! error. If you sort the results, all of the #VALUE!s will be together, then you can fix the dates that aren’t valid and try your sort again.

Dates with DATEVALUE being used
Dates with DATEVALUE being used

Watch the video

Sort Based on Date Values

If you sort your list now, all of the invalid dates are grouped together. Since they have been grouped together, now you can work on fixing each of them until the #VALUE! error goes away. First, highlight the range of data to sort. Be sure to select all of the columns but leave out the header row.

Values to be sorted are highlighted
Highlighted values to sort

After highlighting the range that you want to sort, go to Data validation then Sort range. If you sort without selecting the range, Sheets automatically sorts by column A which is not where your date values are.

Sort range

Sort range

Next, select column B (or whatever columns your dates are in).

Pick the correct column
Pick the correct column

After sorting, all of your invalid dates are grouped together. As you correct each one, the DATEVALUE formula will change from #VALUE! to the serial number representing that date. After they are all fixed, you’re ready to sort again!

Sorted data
Sorted data before fixing the dates

After fixing
After fixing the dates, before re-sorting

Each date sorts as expected now that every date value has been checked and fixed with the help of the DATEVALUE function.

Sorted data after fixing
Fixed and re-sorted correctly

Keep Your Dates Sorted

If you add new dates to your spreadsheet, there are two methods to keep them sorted. You can either resort them manually as discussed above, or use the StaySorted add-on to automatically sort any new entries.

 Live examples in Sheets

Go to this spreadsheet for examples of sorting dates that you can study and use anywhere you would like.

0 thoughts on “Google Sheets – How to Sort Dates into Chronological Order

Leave a Reply