Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting by Birthday
The newsletter editor in my org asked me to sort our members by their
birthday (day month only). I'm using Excel 2003 I highlighted the column, selected format/cells and changed the 5/3/1962 to a format without the year. It looked OK. When I then try to sort by that column, the dates appear random. I tried another day/month format and it changed OK to May-3 but the sort still didn't work. Any ideas? Can excel do this? Thanks, Dennis |
#2
|
|||
|
|||
Dennis,
You need a helper column and extract the day and month. Something like =TEXT(A1,"mmdd") and then sort with that column as the key. -- HTH Bob Phillips "Dennis Hughes" wrote in message ... The newsletter editor in my org asked me to sort our members by their birthday (day month only). I'm using Excel 2003 I highlighted the column, selected format/cells and changed the 5/3/1962 to a format without the year. It looked OK. When I then try to sort by that column, the dates appear random. I tried another day/month format and it changed OK to May-3 but the sort still didn't work. Any ideas? Can excel do this? Thanks, Dennis |
#3
|
|||
|
|||
Bob,
That sounds like the key. Turns out when I drop the year in the dob with the reformatting of the date , the sort works, but still includes the invisible year. This should help get rid of the year. Thanks, Dennis "Bob Phillips" wrote in message ... Dennis, You need a helper column and extract the day and month. Something like =TEXT(A1,"mmdd") and then sort with that column as the key. -- HTH Bob Phillips "Dennis Hughes" wrote in message ... The newsletter editor in my org asked me to sort our members by their birthday (day month only). I'm using Excel 2003 I highlighted the column, selected format/cells and changed the 5/3/1962 to a format without the year. It looked OK. When I then try to sort by that column, the dates appear random. I tried another day/month format and it changed OK to May-3 but the sort still didn't work. Any ideas? Can excel do this? Thanks, Dennis |
#4
|
|||
|
|||
Hi
To understand this, you have to understand the nature of dates in Excel, and what does cell formatting do. Dates in Excel are simply numbers. P.e. when date in cell is 5/3/1962, then really the value in cell is 22769. And p.e. the value of date 12/31/1960 is 22281, which of course is less than 22769. Formatting the cell affects the way the cell value is displayed - it doesn't change real value in cell at all. So what ever format you use for your dates, sorted are they as numbers. Bob's formula returns a text string - the result isn't date anymore. And it is sorted as string too - along with rest of table, when you did all in right way. Arvi Laanemets "Dennis Hughes" wrote in message ... Bob, That sounds like the key. Turns out when I drop the year in the dob with the reformatting of the date , the sort works, but still includes the invisible year. This should help get rid of the year. Thanks, Dennis "Bob Phillips" wrote in message ... Dennis, You need a helper column and extract the day and month. Something like =TEXT(A1,"mmdd") and then sort with that column as the key. -- HTH Bob Phillips "Dennis Hughes" wrote in message ... The newsletter editor in my org asked me to sort our members by their birthday (day month only). I'm using Excel 2003 I highlighted the column, selected format/cells and changed the 5/3/1962 to a format without the year. It looked OK. When I then try to sort by that column, the dates appear random. I tried another day/month format and it changed OK to May-3 but the sort still didn't work. Any ideas? Can excel do this? Thanks, Dennis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
sorting rows wich can vary in number | Excel Discussion (Misc queries) | |||
sorting detail rows - summary row in an outline | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Discussion (Misc queries) | |||
sorting question | Excel Discussion (Misc queries) |