Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Aug 2003
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sorting different date styles (Excel 2000)

    I have a series of entries I need to put in date order in Excel. The problem is some items have day month and year, others only have month and year. For example, the newspaper articles are 1/13/06 and the magazine dates are July, 2005. Is there a date for format that I can use so these all sort together with the month/year only entries either appearing at the beginning or end of that month? I tried changing the month only date to 07/00/05 but that didn't work. thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sorting different date styles (Excel 2000)

    Select one of the cells containing month-and-year.
    Then select Format | Cells...
    Is the cell set to General format, or to a Date or Custom format?

  3. #3
    2 Star Lounger
    Join Date
    Aug 2003
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting different date styles (Excel 2000)

    I had highlighted the whole column and selected dd/mm/yy date format

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sorting different date styles (Excel 2000)

    OK, next question. If you set Horizontal alignment (in the Alignment tab of Format | Cells...) to General, are the cells with month-year left aligned or right-aligned?

  5. #5
    2 Star Lounger
    Join Date
    Aug 2003
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting different date styles (Excel 2000)

    if alignment is general, the cell is aligned left

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sorting different date styles (Excel 2000)

    Try this: say that your dates are in column A, starting in A2 (A1 is a column header).
    If necessary, insert a blank column in column B.
    In B2, enter the following formula:
    <code>
    =IF(ISTEXT(A2),DATEVALUE("1 "&A2),A2)
    </code>
    Fill down as far as needed.
    If you wish, you can format column B with a date format, but that is not essential.
    Select cell B2.
    Click on the Sort Ascending button on the toolbar.
    The data should be sorted on column B, thereby also sorting column A.

    If this doesn't work correctly, could you post a small sample workbook?

  7. #7
    2 Star Lounger
    Join Date
    Aug 2003
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting different date styles (Excel 2000)

    thanks for the tip--it sort of worked. I attached a sample workbook. It didn't like some of the original formatting. The other problem is if the date was July, 2005, it changed it to july1, 2005 which isn't really accurate. thanks for your help

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sorting different date styles (Excel 2000)

    7/00/2003 is not valid as a date - Windows cannot interpret it. You can enter July 2003 or Jul-2003 or Jul/2003 or 7/2003, but not 7/00/2003. Otherwise, the formulas seem to work correctly as far as I can see. If you don't want July, 2005 to be converted to July 1, 2005, what else should it be?

  9. #9
    2 Star Lounger
    Join Date
    Aug 2003
    Posts
    137
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting different date styles (Excel 2000)

    Ideally I would like July, 2005 to go to 7/2005 and go either at the beginning or the end of the other July 2005 dates

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sorting different date styles (Excel 2000)

    The formula I proposed will convert July, 2005 to the first of July, 2005. It will be sorted at the beginning of July. How it is formatted doesn't matter.
    7/2005 in itself is not a valid date. Windows/Excel cannot handle it.

Posting Permissions

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