Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: date sort

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    We have a spreadsheet that contains employees anniversary dates for each month. We need to sort the list by the day the employee started work. Is there a way to sort by the specific anniversary day (i.e. all of the employees hired on the 1st of the month, then the 2nd, etc. regardless of the year they were hired)instead of the numeric equivalent of the anniversary date which results in the dates being sorted first by the year?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could add a column with the formula
    =TEXT(A1,"m-d")
    where your dates are col A.

    Sort by this new column

    good luck

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Close, but "=TEXT(A1,"mm-dd")" will sort better.

    HTH

    Rocky...

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Changing it to text still didn't allow me to do the needed sort. What I ended up doing was using the formula =DAY(A1) (assuming my first date was in cell A1). This formula pulled just the day into a new column. After copying the formula where needed, I was able to sort my list first by the new day column, then by my original date column. This enabled me to display my list in order by the day the employee started and then by the year they started.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-30 11:19, ptebo wrote:
    We have a spreadsheet that contains employees anniversary dates for each month. We need to sort the list by the day the employee started work. Is there a way to sort by the specific anniversary day (i.e. all of the employees hired on the 1st of the month, then the 2nd, etc. regardless of the year they were hired)instead of the numeric equivalent of the anniversary date which results in the dates being sorted first by the year?
    Wait a minute. Read what you said? Give 3 to 5 real dates picked randomly in the there natural order and then the same group sorted, please. Then re-state your sort order for us, please

    This stuff really works, trust me.

    Rocky...

    [ This Message was edited by: Rocky E on 2002-04-30 16:31 ]

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Guys:
    Using the text value for cell A2 in an adjacent still keeps the memory of the year while sorting, so if the year has to be neglected, so for a corresponding date in cell A2, I want to put the following formula in cell B2 ...

    =MONTH(A2)&"-"&DAY(A2)

    then drag down
    then sort on column B in ascending order.

    Hope This Helps!

    N.B.
    I checked with IML and RockyE's formulation -- these do work if you coerce their text values to numeric values, say with +0 before sorting.



    _________________
    Yogi Anand

    Edit: Deleted inactive website from hardcoded signature

    [ This Message was edited by: Yogi Anand on 2003-01-19 13:29 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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