Sorting Dates and Times

by Allen Wyatt
(last updated September 29, 2018)

2

Shelby has a worksheet that contains project information. Among other things, the worksheet contains a date and time in column A. She would like to sort the projects in the worksheet based on this date and time, but when she sorts the information the projects don't show in chronological order. She wonders why this is occurring.

Normally this occurs because the dates and times in column A aren't really stored, internally, in Excel's date and time format. Perhaps they were entered into a column that was previously formatted as text, or perhaps they were imported from an external source and Excel couldn't recognize them as dates.

There are a couple of easy ways to check whether Excel thinks those cells are dates. First, just select one of the dates in column A and then look at the Home tab of the ribbon. If Excel thinks it is a date, there is a very good chance that the Number Format drop-down list (in the Number group) will say "Date." If it says any other format, then chances are that Excel doesn't recognize it as a date.

You can also select the data that needs to be sorted and display the Sort dialog box. In that dialog box, indicate that you want to sort by Column A. If Excel recognizes the column as containing dates, then the Order drop-down list will change to "Oldest to Newest." If it is recognized as something else, it will show as "A to Z" (for text) or "Smallest to Largest" (for numeric values). When you are done checking, press Esc to close the Sort dialog box without actually sorting.

Finally, another way you can check is to use this formula:

=ISTEXT(A1)

Copy the formula down as many rows as necessary, and any result that shows TRUE is indicating a date, in column A, that Excel thinks is really text.

If you determine that the dates in column A are not being stored as dates, then you need to get them converted. There are numerous ways to do this, depending on what is actually stopping Excel from seeing the dates as dates. For instance, if the cells have a leading space in them, then you need to get rid of that leading space. If the dates are notated in a format that Excel doesn't think is a real data format (such as with periods between date elements), then you need to create a formula that picks the desired parts from the date and constructs a date that Excel can understand.

This will obviously take some experimentation, as the number of ways that dates can be displayed in non-date format are legion. Once the conversion to date format is finished, however, you should be able to sort them in chronological order with absolutely no problem.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5678) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

The Line that Won't Go Away

Have you ever had a line appear on your document that you can't seem to get rid of? It could be due to a built-in ...

Discover More

Maximum Length Limit for a Macro

Make your macros too long, and Excel may just refuse to run them at all. This tip explains what the limit is for macros, ...

Discover More

Inadvertantly Getting Rid of Frozen Panes

Excel provides quite a bit of flexibility in displaying your data. You can have multiple windows visible for the same ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Sorting by the Last Digits in Variable Length Data

Excel is great at sorting information in a worksheet. Sometimes it can be a bit confusing as to how to set up the sort ...

Discover More

Importing Custom Lists

Custom lists are handy ways to enter recurring data in a worksheet. Here's how you can import your own custom lists from ...

Discover More

Ignoring Selected Words when Sorting

If you use Excel to maintain a list of text strings (such as movie, book, or product titles), you may want the program to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 9?

2018-10-01 06:58:17

Terence

I like to use the 'text to column' tool from the data tab for converting text to dates.
You don't add in any separation criteria, then when selecting the data format select date.
This isn't guaranteed but works more often than not in my experience


2018-09-30 04:18:41

Hans

When it comes to sorting dates you really can run into a lot of issues.
I assist people with their Excel files and also when working with different country settings you run into strange and unexpected sorting issues, I always advice to show the date as yyyymmdd but that doesn't always look nice on the worksheet.
I add an extra (hidden) column with a formula that is formatted long and the date (an time) as serial value and use this as the sort column.
It's a little extra work but resolves quite a few issues.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.