Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Lauderhill, Florida, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort dates by year (Excel 2000)

    1. How do I sort a column of dates in mm/dd/yyyy format by yyyy ?

    2. How do I format cells in the International date format yyyy-mm-dd ? (This would make sorting by year a no-brainer.)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort dates by year (Excel 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 21-Oct-03 12:40. Corrected typo)</P>Using: Data - sort as you would sort anything else.

    It does not matter what the display format is, the dates will sort ascending (or descending as desired) in chronological order. The value of the date does not change only the display with the format.

    The date value is a number (it is the number of days past Dec 31, 1899) and it will sorted as such.

    To display in yyyy-mm-dd
    format - cells - custom enter (no quotes)
    "yyyy-mm-dd"

    Could you be more specific about what you need.

    Steve

  3. #3
    Lounger
    Join Date
    Mar 2002
    Location
    Lauderhill, Florida, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort dates by year (Excel 2000)

    Thanks for your response, Steve.

    When I sort the column containing the mm/dd/yyyy dates, it sorts first by mm then dd then yyyy order.

    I want to sort the column first by yyyy. Is this possible without having to re-enter hundreds of dates in reverse (yyyy-mm-dd) order?

    Chuck

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sort dates by year (Excel 2000)

    In that case they look like dates but are not recognized by Excel as dates. Can you attach a workbook with a sample so we can determine what the format really is?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort dates by year (Excel 2000)

    It sounds like your dates are stored as text strings, and not dates. That would explain the sort behavior you're describing. Use the DATEVALUE function to convert a date string to an actual date and sort on that.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort dates by year (Excel 2000)

    I will guess that your "dates" are not really dates, but are instead "text" that looks like a date. TO test, change the format - display to general. If they are still the "dates" the "dates" are text and not real dates.

    If so then
    Highlight the column
    choose text to columns
    delimited
    <finish>

    Excel should convert them. Then they can be formatted as desired and sorted.

    If formatting as general reveals number, you will have to give more details, as I do not understand why they don't sort correctly.

    Steve

  7. #7
    Lounger
    Join Date
    Mar 2002
    Location
    Lauderhill, Florida, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort dates by year (Excel 2000)

    Thanks again for the great info, Steve. This solved the sorting problem. And thanks to all who offered advice.

    In the meantime, I discovered the solution to my problem of displaying the date in International format (yyyy-mm-dd). The answer is in the Date tab of the Control Panel's Regional Options applet.

    Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •