PC Review


Reply
Thread Tools Rate Thread

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

 
 
=?Utf-8?B?U01XODIw?=
Guest
Posts: n/a
 
      21st Jun 2006
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
 
Reply With Quote
 
 
 
 
Daniel CHEN
Guest
Posts: n/a
 
      21st Jun 2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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



 
Reply With Quote
 
 
 
 
=?Utf-8?B?TWFyY2Vsbw==?=
Guest
Posts: n/a
 
      21st Jun 2006
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

 
Reply With Quote
 
=?Utf-8?B?U01XODIw?=
Guest
Posts: n/a
 
      21st Jun 2006
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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

>
>
>

 
Reply With Quote
 
=?Utf-8?B?U01XODIw?=
Guest
Posts: n/a
 
      21st Jun 2006
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

 
Reply With Quote
 
daddylonglegs
Guest
Posts: n/a
 
      21st Jun 2006

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...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=554245

 
Reply With Quote
 
=?Utf-8?B?U01XODIw?=
Guest
Posts: n/a
 
      22nd Jun 2006
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...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=554245
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Avg. 1-month, 3-month, 6-month & 12-month Stock Returns ryguy7272 Microsoft Access Queries 2 7th Feb 2010 04:28 PM
Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? misscrf Microsoft Excel Misc 1 14th Dec 2007 03:59 PM
Print Current Month with last month and next month in Header JMoore0203 Microsoft Outlook Calendar 1 5th Dec 2007 05:15 PM
printing Little Current month and Little Next month on Banner when it should little PRIOR month and little Next month. jake_allen10@hotmail.com Microsoft Outlook 0 3rd Nov 2006 08:30 PM
Converting Month/Date/Year data & sorting by month/date only =?Utf-8?B?Uy4gV2FzaGluZ3Rvbg==?= Microsoft Excel Worksheet Functions 4 26th Apr 2004 05:01 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:36 PM.