Share via

Date values not sorting

Anonymous
2023-01-03T15:43:45+00:00

I have a data set with date values that I want to sort from oldest to newest. The sort isn't working properly.

Initially the field had date and time. I split the field using 'space' as the delimiter. The field values are in the format mm/dd/yyyy (e.g., 1/10/2011). The values still will not sort properly.

When I attempt to sort on the date column, the sort query defaults to A to Z for Order. That says the value is not date/numeric but text. The Datevalue function doesn't work because the field is not in the proper format. I've tried a copy/paste special on Value and that doesn't work.

Useful thoughts appreciated.

Thank you.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-01-05T06:53:43+00:00

    Hello, I am Leonielhou, an Independent Advisor and a user like you, I am happy to help clarify any questions you may have.

    I believe the values in the date field are being treated as text rather than dates, which is why the sort isn't working as expected and the DATEVALUE function isn't working.

    One way to fix this would be to use the DATE function to convert the text values to actual dates. You can use the DATE function like this:

    =DATE(year, month, day)

    You can extract the year, month, and day values from the text field using the YEAR, MONTH, and DAY functions, respectively. For example:

    =DATE(YEAR(A2), MONTH(A2), DAY(A2))

    This will convert the text in cell A2 to a date value. You can then copy this formula down to the other cells in the column to convert all the values to dates.

    Once the values are converted to dates, you should be able to sort the column by date.

    I hope I am able to give valuable information based on what I have understood on your concern.

    80+ people found this answer helpful.
    0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. riny 20,870 Reputation points Volunteer Moderator
    2023-01-03T17:29:25+00:00

    When you split the date and time with space as the delimiter, did you set the data type for the date column to be Date and MDY? (Step 3 of 3 in Text-to-columns)

    If not, your "dates" are just texts and will sort in alphabetical order. That is, not in date order.

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2023-01-04T01:28:38+00:00

    That says the value is not date/numeric but text

    convert to date then sort。

    can you share your Excel file？

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2023-01-03T17:21:57+00:00

    I did attempt to sort the data before splitting the cells. That didn't work either. I suspect the time values were messing up the sort function, which is why I split the values into two cells.

    0 comments
  4. Anonymous
    2023-01-03T16:37:09+00:00

    Hi RobertFisherCda!

    Thank you for writing to the Microsoft Answer Community Forum. I am Shakiru, an independent advisor and a user like you.

    Excel will not be able to sort dates appropriately if the format cell (Date Value) is not in the correct date format. That is, it should not be in text form

    Have you tried to sort the value of the dates before applying the spaces to them?

    I suggest you tried that first.

    Kind Regards, Shakiru

    0 comments