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

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

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

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