Sorting by Birthday

[Dennis Hughes]

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

 

[Bob Phillips]

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.

 

[Dennis Hughes]

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

 

[Arvi Laanemets]

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