Written by Allen Wyatt (last updated September 23, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 Microsoft 365.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Need to sort all the data in a table? Here's the fastest and easiest way to do it.
Discover MoreNeed to sort your data based on the color of the cell or the color of the text within the cell? Excel makes it easy to do ...
Discover MoreIf you use graphics in a worksheet that are associated with certain cells (perhaps images of parts or icons for worksheet ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-09-24 11:51:29
J. Woolley
If column A has date/time values as text or a mixture of text and date serial numbers, you might be able to convert all to date serial numbers as follows:
1. Select the cells
2. Pick Data > Data Tools > Text to Columns (Alt+A+E)
3. In the Text to Columns Wizard:
3.a. Pick Delimited, then click Next
3.b. Clear all delimiters, then click Next
3.c. Pick Date for column data format, then click Finish
Alternatively, you might use the Wizard to separate date/time text values into MM DD YY hh mm ss, then re-combine them into date serial numbers using Excel's DATE and TIME functions.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments