Sort month/date/year data using month and date only

Discussion in 'Microsoft Excel Misc' started by Guest, Jun 21, 2006.

  1. Guest

    Guest Guest

    Hi,

    I am attempting to sort a list of companies that are formatted in our
    database system by month/date/year (mm/dd/yyyy). I want them to sort by
    month and date only, however Excel 2000 recognizes the year as a criteria.

    How do I get the program to ignore the year information for these 4,000+
    entries?

    Thanks so much.
    SMW820
     
    Guest, Jun 21, 2006
    #1
    1. Advertisements

  2. Guest

    Daniel CHEN Guest

    An indirect way:
    Insert two extra columns - one for month using MONTH function and another
    for day using DAY function.
    Then sort by month and day.

    --
    Best regards,
    ---
    Yongjun CHEN
    ==================================
    - - - - www.XLDataSoft.com - - - -
    Free Excel/VBA Tool & Training Material
    ==================================
    "SMW820" <> wrote in message
    news:...
    > Hi,
    >
    > I am attempting to sort a list of companies that are formatted in our
    > database system by month/date/year (mm/dd/yyyy). I want them to sort by
    > month and date only, however Excel 2000 recognizes the year as a criteria.
    >
    > How do I get the program to ignore the year information for these 4,000+
    > entries?
    >
    > Thanks so much.
    > SMW820
     
    Daniel CHEN, Jun 21, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    Hi,

    you could use an auxiliar column with =month(a1)&day(a1) copy it down and
    sort by this column

    HTH
    Regards from Brazil
    Marcelo

    "SMW820" escreveu:

    > Hi,
    >
    > I am attempting to sort a list of companies that are formatted in our
    > database system by month/date/year (mm/dd/yyyy). I want them to sort by
    > month and date only, however Excel 2000 recognizes the year as a criteria.
    >
    > How do I get the program to ignore the year information for these 4,000+
    > entries?
    >
    > Thanks so much.
    > SMW820
     
    Guest, Jun 21, 2006
    #3
  4. Guest

    Guest Guest

    Hi Daniel:

    Thank you for your solution. At first I did not understand. After reading
    the second response from Marcelo, I got it! And it worked!

    Thanks again.
    SMW820

    "Daniel CHEN" wrote:

    > An indirect way:
    > Insert two extra columns - one for month using MONTH function and another
    > for day using DAY function.
    > Then sort by month and day.
    >
    > --
    > Best regards,
    > ---
    > Yongjun CHEN
    > ==================================
    > - - - - www.XLDataSoft.com - - - -
    > Free Excel/VBA Tool & Training Material
    > ==================================
    > "SMW820" <> wrote in message
    > news:...
    > > Hi,
    > >
    > > I am attempting to sort a list of companies that are formatted in our
    > > database system by month/date/year (mm/dd/yyyy). I want them to sort by
    > > month and date only, however Excel 2000 recognizes the year as a criteria.
    > >
    > > How do I get the program to ignore the year information for these 4,000+
    > > entries?
    > >
    > > Thanks so much.
    > > SMW820

    >
    >
    >
     
    Guest, Jun 21, 2006
    #4
  5. Guest

    Guest Guest

    Hi Marcelo:

    Thank you for your response. I had received another similar solution prior
    to yours, and did not understand. I gained clarity after reading your
    answer. And it worked!

    Thanks so much.
    SMW820


    "Marcelo" wrote:

    > Hi,
    >
    > you could use an auxiliar column with =month(a1)&day(a1) copy it down and
    > sort by this column
    >
    > HTH
    > Regards from Brazil
    > Marcelo
    >
    > "SMW820" escreveu:
    >
    > > Hi,
    > >
    > > I am attempting to sort a list of companies that are formatted in our
    > > database system by month/date/year (mm/dd/yyyy). I want them to sort by
    > > month and date only, however Excel 2000 recognizes the year as a criteria.
    > >
    > > How do I get the program to ignore the year information for these 4,000+
    > > entries?
    > >
    > > Thanks so much.
    > > SMW820
     
    Guest, Jun 21, 2006
    #5
  6. Marcelo Wrote:
    > Hi,
    >
    > you could use an auxiliar column with =month(a1)&day(a1) copy it down
    > and
    > sort by this column
    >
    > HTH
    > Regards from Brazil
    > Marcelo
    >
    >


    I think you'd have a problem with this approach. 11th January and 1st
    november would both become 111.

    For a one column approach try

    =DATE(0,MONTH(A1),DAY(A1))


    --
    daddylonglegs
    ------------------------------------------------------------------------
    daddylonglegs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30486
    View this thread: http://www.excelforum.com/showthread.php?threadid=554245
     
    daddylonglegs, Jun 21, 2006
    #6
  7. Guest

    Guest Guest

    Hi "daddylonglegs",

    Yes, I can see where this might become an issue and luckily I did not have
    any of these dates in the system. However, I will also keep this version of
    the recommended solutions on hand for future reference.

    Thanks very much for your help.
    SMW820

    "daddylonglegs" wrote:

    >
    > Marcelo Wrote:
    > > Hi,
    > >
    > > you could use an auxiliar column with =month(a1)&day(a1) copy it down
    > > and
    > > sort by this column
    > >
    > > HTH
    > > Regards from Brazil
    > > Marcelo
    > >
    > >

    >
    > I think you'd have a problem with this approach. 11th January and 1st
    > november would both become 111.
    >
    > For a one column approach try
    >
    > =DATE(0,MONTH(A1),DAY(A1))
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30486
    > View this thread: http://www.excelforum.com/showthread.php?threadid=554245
    >
    >
     
    Guest, Jun 22, 2006
    #7
    1. Advertisements

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Guest

    Sort by Month and Year

    Guest, Feb 2, 2006, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    229
    Dave Peterson
    Feb 2, 2006
  2. Guest
    Replies:
    1
    Views:
    298
    Dave Peterson
    Apr 25, 2006
  3. Guest

    Matching the month and year portion of a date only

    Guest, Jun 9, 2006, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    190
    Guest
    Jun 9, 2006
  4. vikkam

    pick up date, month and year from a date

    vikkam, Jul 27, 2006, in forum: Microsoft Excel Misc
    Replies:
    4
    Views:
    254
    vikkam
    Jul 27, 2006
  5. Guest

    date format only showing month year

    Guest, Apr 30, 2007, in forum: Microsoft Excel Misc
    Replies:
    4
    Views:
    361
    David Biddulph
    Apr 30, 2007
  6. misscrf
    Replies:
    1
    Views:
    666
    Pete_UK
    Dec 14, 2007
  7. Visha

    Sort by date month and not year

    Visha, Dec 17, 2007, in forum: Microsoft Excel Misc
    Replies:
    4
    Views:
    220
    David Biddulph
    Dec 17, 2007
  8. EnviroGeek

    Date picker scrollbar month and year only

    EnviroGeek, Feb 24, 2010, in forum: Microsoft Excel Misc
    Replies:
    19
    Views:
    1,098
    Dave Peterson
    Mar 4, 2010
Loading...