Sorting Dates and Times

by Allen Wyatt
(last updated February 3, 2017)

19

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, and 2013.

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

Searching for a Term in Sheets

Got a need to search the web for more information on something within a spreadsheet? Believe it or not, Sheets provides a ...

Discover More

Inserting Video into Worksheets

You can add all sorts of objects to your workbooks, including video clips. Here's the pros and cons (along with the how-to) ...

Discover More

Multi-Page Print Preview

Many users rely on Print Preview to show them what their printout will look like. When using Print Preview, you aren't ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

MORE EXCELTIPS (RIBBON)

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

Discover More

Can't Sort Imported Data

Import information from an external database, and you'll no doubt want to use Excel's simple tools to manipulate that data. ...

Discover More

Sorting for a Walking Tour

Want to sort addresses by even and odd numbers? By using a formula and doing a little sorting, Excel can return the addresses ...

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 for this tip:

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. 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 seven minus 6?

2017-02-09 09:07:15

Shandor

Another workaround for problematic dates and numbers that might or might not be text: Format a spare column in Excel as Date or Number depending on your needs. In your troubled column, copy the original dates or numbers, paste into Notepad, select and copy those Notepad values, paste back into Excel, but into the new column formatted as you require. Paste plainly, don't paste as values. Notepad has a strong cleansing effect!


2017-02-08 16:47:12

Tom Speake

Michael, the TYPE() function does some of what you are asking for with your WHATIS() function.


2017-02-08 11:38:37

Peter Atherton

Michael & Will

The first comment left by Micky might be helpful. Drill down to the bottom. I hadn't heard of this trick before.


2017-02-08 10:20:43

MIchael Armstrong

I often have data in PDF files (e.g., credit card statements) that I can copy and paste into Excel, resulting a columns that look "right". In particular, data in a Date column might appear as "mm/dd/yyyy" but it's not formatted as Date, can't be manipulated with text functions, and can't be used in computation. Usually, just formatting the data as Date solves the problem.

Excel badly needs a "Whatis()" function that would tell you how Excel has treated such imported data.


2017-02-07 21:01:51

Will LOURIER

Thanks, this has worked on some cases.
I now have a sheet with date+time columns written as follows:
dd mmm yy - hh:mm

The data format is "general"

Sorting these columns puts them in order of the dd value.
Formatting the columns to DATA format has no effect.

Can these columns be changed to a sortable date format?

Thanks and best regards
Will


2017-02-03 10:28:08

Shandor

The article and comments are super! Yet, now that Microsoft's commercial reach has made Bill Gates and many others tens of billion$ of personal wealth, they should make Excel deal with the date world's well known characteristics automatically. It's high time for them to see that most of their software has commoditized and should do more of the work for us.


2017-02-03 09:48:58

Debi

Is there a way to import dates from an excel spreadsheet directly to your outlook calendar or to multiple calendars?


2017-02-03 07:29:54

Napier

Great tip as I frequently import data with date columns into Excel and have problems sorting. How about a simple VBA program to correct the date format problems?


2016-09-30 18:01:49

Heidi-Jo

Micky,

You are a life-savior! Thank-you I never would have thought about using text to columns to fix this issue. It worked beautifully and saved me many many hours.


2016-08-10 20:04:50

steven cantrell

your web site solved my problem in 2 minutes thank you


2016-06-29 08:49:41

suresh

i cannot able to sort the dates and tried the tips given by "Michael (micky) Avidan"
it is helped me temporarily. i want to fix it for a new file too without repeating all the steps again


thanking you in advance
awaiting for reply.


2016-04-25 05:23:47

Mehmet Osman

I have a basic Excel spreadsheet where I am listing each case I work on. There are about 50 lines for 50 cases.

I didn't put the date in. I sorted the list into alphabetical but now I want it back in the original order, basically in order of the last case I dealt with. But I simply cannot find an option for this. Can anyone help?

Thanks.
M


2016-04-22 22:26:11

Brendan

Great buddy ! thank you. I formatted the whole column as dates and then retyped the ones that would not go into date order. They must have had spaces or something on the first data entry.


2015-12-01 17:36:35

Greg

Michael (micky) Avidan answer helped me the most. Thanks!


2015-06-25 01:33:03

Harsha

Thanks..It is helpful for me also..:)


2013-07-15 11:53:08

Brenda

Good article and helpful replies. Thank you to all. I use dates a lot.


2013-07-15 09:52:42

Ciaran

Michael has a good suggestion: the Text to columns tool is really handy. But it can fail if you don't pay attention to the date format in the date box to the right of the date radio button. What you are doing here is telling Excel what the existing date format is (and not the format you wish to end up with). Excel will then convert the date to your Windows default date format.

You can also use this technique in the Text Import Wizard.


2013-07-15 09:43:46

JC

Micky, thanks for the tip. I would never have looked within the "Text to Columns" wizard to convert those values, but I'll surely remember it for future reference. It would be nice if I could select the range and use the "Format Cells" option to format the values (It would seem to be the most intuitive way to me).

As an alternative, if the date is formatted as text I arrange it in yyyy-mm-dd hh:mm:ss format and sorting will still work. This works fine for me as long as I won't be needing to use the Filter tool on the column.


2013-07-13 04:57:59

Michael (micky) Avidan

In 99% of the above "Not a real date" examples - there is a quick & easy way to overcome the "problem".

1) Select the range of, so called, "dates".
2) In the ribbon: 'Data' > 'Text to columns' > 'Next' > 'Next' > 'select the Date radio button > 'End'

Michael Avidan
“Microsoft®” MVP – Excel
ISRAEL


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.

Links and Sharing
Share