Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Robert Judge
 
Posts: n/a
Default How to sort by day and month while ignoring year?

I have a list of birthdays including month, day and year. I would like to
sort the list by month and day but ignore the year. How can I do this? I
will appreciate advice.
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

You could use 2 help columns with formulas, assume the dates are in A and in
B put

=MONTH(A2)

in C

=DAY(A2)


copy down and then select all 3 columns, sort by first B and then C

or use just one help column (B) and an arbitrary date like this year

=DATE(2004,MONTH(A2),DAY(A2))

or

=DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))

copy down and sort by the help column


Regards,

Peo Sjoblom

"Robert Judge" wrote:

I have a list of birthdays including month, day and year. I would like to
sort the list by month and day but ignore the year. How can I do this? I
will appreciate advice.

  #3   Report Post  
Don Guillett
 
Posts: n/a
Default

An additional column with this and then sort both using this as key.

=TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00")
or
=MONTH(A1) &"/" & DAY(A1)

--
Don Guillett
SalesAid Software

"Robert Judge" wrote in message
...
I have a list of birthdays including month, day and year. I would like to
sort the list by month and day but ignore the year. How can I do this? I
will appreciate advice.



  #4   Report Post  
Robert Judge
 
Posts: n/a
Default

OK, thanks. This is a list of ancestor's birthdays, and some are in the
nineteenth century. Your formula worked for dates with years in the
twentieth century. However, when the year in in the nineteenth century, the
formula returns an error message of "#VALUE!" How can I correct that?

"Peo Sjoblom" wrote:

You could use 2 help columns with formulas, assume the dates are in A and in
B put

=MONTH(A2)

in C

=DAY(A2)


copy down and then select all 3 columns, sort by first B and then C

or use just one help column (B) and an arbitrary date like this year

=DATE(2004,MONTH(A2),DAY(A2))

or

=DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))

copy down and sort by the help column


Regards,

Peo Sjoblom

"Robert Judge" wrote:

I have a list of birthdays including month, day and year. I would like to
sort the list by month and day but ignore the year. How can I do this? I
will appreciate advice.

  #5   Report Post  
Gord Dibben
 
Posts: n/a
Default

Robert

John Walkenbach has a free downloadable add-in for working with pre-1900
dates.

http://j-walk.com/ss/excel/usertips/tip028.htm

Gord Dibben Excel MVP

On Thu, 23 Dec 2004 14:27:03 -0800, Robert Judge
wrote:

OK, thanks. This is a list of ancestor's birthdays, and some are in the
nineteenth century. Your formula worked for dates with years in the
twentieth century. However, when the year in in the nineteenth century, the
formula returns an error message of "#VALUE!" How can I correct that?

"Peo Sjoblom" wrote:

You could use 2 help columns with formulas, assume the dates are in A and in
B put

=MONTH(A2)

in C

=DAY(A2)


copy down and then select all 3 columns, sort by first B and then C

or use just one help column (B) and an arbitrary date like this year

=DATE(2004,MONTH(A2),DAY(A2))

or

=DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))

copy down and sort by the help column


Regards,

Peo Sjoblom

"Robert Judge" wrote:

I have a list of birthdays including month, day and year. I would like to
sort the list by month and day but ignore the year. How can I do this? I
will appreciate advice.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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



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

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"