Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Month Sort (2000)

    I have a large spreadsheet containing birth dates (05/12/56) I'm trying to sort the list by the months they were born then the day and last the year. I can only get it to sort in order by the year. I would like it so everyone born in the month of January will be first regardless of what year they were born.
    Thanks for any help

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Month Sort (2000)

    Hi,

    If you insert 2 columns next to the date column, the first can be the DAY and the second the MONTH. In the cell beside the first date enter "=DAY(A1)" where A1 is the Date cell. You should see that the Day No of the Date is now showing in this cell.

    In the second column enter "=MONTH(A1)" and you should now have the month no. If you copy these 2 formulas down beside the dates you will have Day Nos and Month Nos for all dates.

    You can now sort by Month and Day as you wish.

    Good Luck
    Peter Moran

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Month Sort (2000)

    Steve: I think that will only sort on month, not month day year as requested.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Month Sort (2000)

    In an empty column, in the cell on the same row as the first birth date insert the formula below replacing A1 with the cell address of the cell containing the birth date:

    <pre>=TEXT(A1,"mmddyyyy")
    </pre>


    Copy the formula down to all of the rows with birth dates. Sort on this column.
    Legare Coleman

  5. #5
    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: Month Sort (2000)

    It would sort on Month day but not year (I missed the year part in the questions <img src=/S/blush.gif border=0 alt=blush width=15 height=15>).

    Steve

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Month Sort (2000)

    Then you have a bug in the code -- Day(1)
    Legare Coleman

  7. #7
    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: Month Sort (2000)

    <P ID="edit" class=small>(Edited by sdckapr on 18-May-04 14:41. Corrected the typo [thank you, Legare})</P>In addition to Perter's suggestion, you could make 1 column:
    You could use 1 column with the formula:
    =date(year(today()),month(A1),day(A1))
    this ives the date of their birthday this year and you can sort this column.

    Steve

  8. #8
    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: Month Sort (2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I meant A1 (and I have corrected the post)

    Steve

Posting Permissions

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