Date sorting in excel

Discussion in 'NZ Computing' started by Craig Shore, Dec 20, 2005.

  1. Craig Shore

    Craig Shore Guest

    Just wondering if this is possible.

    In a column full of dates with date, month and year, we want to sort it by the
    month. Even when we've only got the month and date visible (by formatting the
    cells) it still seems to sort it on the year first.

    Is it possible, and if so how?

    TIA.
    Craig Shore, Dec 20, 2005
    #1
    1. Advertising

  2. Craig Shore

    Armpit Guest

    "Craig Shore" <> wrote in message
    news:eek:...
    > Just wondering if this is possible.
    >
    > In a column full of dates with date, month and year, we want to sort it by
    > the
    > month. Even when we've only got the month and date visible (by formatting
    > the
    > cells) it still seems to sort it on the year first.
    >
    > Is it possible, and if so how?
    >
    > TIA.


    Dunno if it is that way, but you could always make a second (hidden) column
    with a datepart bit that only has the month, or month + day, and sort that
    way.
    Armpit, Dec 20, 2005
    #2
    1. Advertising

  3. Craig Shore

    Alan Guest

    "Craig Shore" <> wrote in message
    news:eek:
    > Just wondering if this is possible.
    >
    > In a column full of dates with date, month and year, we want to sort
    > it by the month. Even when we've only got the month and date
    > visible (by formatting the cells) it still seems to sort it on the
    > year first.
    >
    > Is it possible, and if so how?
    >
    > TIA.
    >


    Hi Craig,

    I don't think you will be able to do that, since as far as excel is
    concerned, the dates are actually values (probably around 38,000)
    which is the number of days since 1 Jan 1900.

    When you sort, you are really sorting a list of numbers.

    To sort by month, you could add a helper column that pulls out the
    month only and then sort by that column.

    If your dates are in A2:A99 then put the following in a new column:

    =Text(A1,"MM")

    That will return a two digit string representing the month (e.g. Dec =
    "12") which you could then use to sort.

    Once sorted, you could hide that column or delete it altogether if you
    want.

    HTH,

    Alan.

    --
    The views expressed are my own, and not those of my employer or anyone
    else associated with me.

    My current valid email address is:



    This is valid as is. It is not munged, or altered at all.

    It will be valid for AT LEAST one month from the date of this post.

    If you are trying to contact me after that time,
    it MAY still be valid, but may also have been
    deactivated due to spam. If so, and you want
    to contact me by email, try searching for a
    more recent post by me to find my current
    email address.

    The following is a (probably!) totally unique
    and meaningless string of characters that you
    can use to find posts by me in a search engine:

    ewygchvboocno43vb674b6nq46tvb
    Alan, Dec 21, 2005
    #3
  4. Craig Shore

    Peter Guest

    Alan wrote:
    > If your dates are in A2:A99 then put the following in a new column:
    > =Text(A1,"MM")


    Or in the new column, use
    =Month(A2)
    and copy this down
    then sort on this new column.

    Depending how you want to process the data, you might find using a pivot
    table worthwhile.

    HTH

    Peter
    Peter, Dec 21, 2005
    #4
  5. Craig Shore

    Craig Shore Guest

    On Wed, 21 Dec 2005 19:02:35 +1300, Peter <> wrote:

    >Alan wrote:
    >> If your dates are in A2:A99 then put the following in a new column:
    >> =Text(A1,"MM")

    >
    >Or in the new column, use
    > =Month(A2)
    >and copy this down
    >then sort on this new column.
    >
    >Depending how you want to process the data, you might find using a pivot
    >table worthwhile.


    Thanks guys, will give it a go tomorrow. At work they send out birthday cards
    and a voucher for a free muffin to all staff. They were maintaining a seperate
    database of this information, but it's too time consuming to keep it up to date
    with new staff and who has left. They were looking at dropping it, but decided
    perhaps they could export the data out of the payroll and use that requiring
    minimal work to get the information. But they struck the above problem. My
    incentive to find an answer - I still get my free muffin next year now :)
    Actually the real motivation was I thought there must be a way and it was
    bugging me not being able to do it when they asked me if it was possible.

    Thanks.
    Craig Shore, Dec 21, 2005
    #5
  6. Craig Shore

    Alan Guest

    "Craig Shore" <> wrote in message
    news:
    >
    >Actually the real motivation was I thought there
    > must be a way and it was bugging me not being able to do it when

    they
    > asked me if it was possible.
    >
    > Thanks.


    I usually find that, when the question is along the lines of, "Can
    Excel do ... XXXX" then answer is nearly always "Yes" (unless the one
    asking the question seriously off the planet).

    However, a better question (or a good follow up one) is:

    "Should we do this in excel or use a different tool?"

    The answer to that is often that excel is not the best tool for the
    job even though it can be bent fit the requirements.

    Alan.

    --
    The views expressed are my own, and not those of my employer or anyone
    else associated with me.

    My current valid email address is:



    This is valid as is. It is not munged, or altered at all.

    It will be valid for AT LEAST one month from the date of this post.

    If you are trying to contact me after that time,
    it MAY still be valid, but may also have been
    deactivated due to spam. If so, and you want
    to contact me by email, try searching for a
    more recent post by me to find my current
    email address.

    The following is a (probably!) totally unique
    and meaningless string of characters that you
    can use to find posts by me in a search engine:

    ewygchvboocno43vb674b6nq46tvb
    Alan, Dec 22, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ~~AAJakeleg

    Help sorting data in Excel

    ~~AAJakeleg, Jun 25, 2004, in forum: Computer Support
    Replies:
    16
    Views:
    631
    Edward Greene
    Jun 28, 2004
  2. Tones

    how to change DATE to WEEK in EXCEL

    Tones, Oct 17, 2004, in forum: Computer Information
    Replies:
    0
    Views:
    5,561
    Tones
    Oct 17, 2004
  3. jasonwoodrun

    Excel date mystery

    jasonwoodrun, Mar 8, 2006, in forum: Computer Support
    Replies:
    11
    Views:
    666
    Dan Evans
    Mar 10, 2006
  4. Geopelia

    sorting messages in date order

    Geopelia, Jul 28, 2006, in forum: NZ Computing
    Replies:
    13
    Views:
    490
    Allistar
    Jul 29, 2006
  5. Replies:
    1
    Views:
    335
Loading...

Share This Page