Xcel 2003. Similar to an earlier thread. I have verified that all cells are
formatted as numbers, dates 00/00. Some of the items in the column will
sort, but some will not. I have cleared the print area; I have Unhid the
superfluous columns, thinking I might have to sort the birthdates and THEN
hide. No joy.

Can you post some examples of what you have and where the data is not
sorting correctly?

If the birthdays have been entered as dd/mm, then Excel will take this
as meaning a date and will assume the current year. Formatting might
allow you to hide the year, but it is still there if those are correct
Excel dates. You might have some dates of birth with the correct year,
but as those are hidden then it might appear that the dates have been
sorted incorrectly. Format the cells as dd/mm/yyyy (temporarily) to
see what is really in those cells.

Another problem might be that you have entered dd/mm as a text value,
and these will sort in a different way. They will be unaffected by
changing the format to dd/mm/yy.

Hope this helps.

Pete

On Jan 12, 6:33*pm, 4most wrote:
Xcel 2003. *Similar to an earlier thread. *I have verified that all cells are
formatted as numbers, dates 00/00. *Some of the items in the column will
sort, but some will not. *I have cleared the print area; I have Unhid the
superfluous columns, thinking I might have to sort the birthdates and THEN
hide. *No joy.

Checking the format of the cell isn't enough. You have to check to see if the
value in the cell is numeric:

=isnumber(a1)
is one way.

But if the values are really dates, then excel will use the year if you sort by
this column. Even if you can't see the year in the cell!

You could add another column to the the data range and sort the range by that
helper column.

=text(a1,"mm/dd")

This is just a string (not a date) and doesn't include the year.

4most wrote:

Xcel 2003. Similar to an earlier thread. I have verified that all cells are
formatted as numbers, dates 00/00. Some of the items in the column will
sort, but some will not. I have cleared the print area; I have Unhid the
superfluous columns, thinking I might have to sort the birthdates and THEN
hide. No joy.

--

Dave Peterson

"Pete_UK" wrote:

Can you post some examples of what you have and where the data is not
sorting correctly?

If the birthdays have been entered as dd/mm, then Excel will take this
as meaning a date and will assume the current year. Formatting might
allow you to hide the year, but it is still there if those are correct
Excel dates. You might have some dates of birth with the correct year,
but as those are hidden then it might appear that the dates have been
sorted incorrectly. Format the cells as dd/mm/yyyy (temporarily) to
see what is really in those cells.

Another problem might be that you have entered dd/mm as a text value,
and these will sort in a different way. They will be unaffected by
changing the format to dd/mm/yy.

Hope this helps.

Pete

On Jan 12, 6:33 pm, 4most wrote:
Xcel 2003. Similar to an earlier thread. I have verified that all cells are
formatted as numbers, dates 00/00. Some of the items in the column will
sort, but some will not. I have cleared the print area; I have Unhid the
superfluous columns, thinking I might have to sort the birthdates and THEN
hide. No joy.

No yyyy is in the spreadsheet, merely dd/mm.



Nelson Helen 11/3
Cheresnuik Audrey 11/6
Smith Gordon 11/27
Kinsella Joan 12/26
Macon Nina 12/30
Miller Beverly 1/26
Patrick Therese 3/18
Luft Marion 3/29
Hallberg Betty 4/15
Cripe Betty 4/22
Wheat Rosemary 4/25
Broas Jackie 5/18
Hongsermeier Wilma 6/19
Rodgers Arlene 6/26
Sattel Sue 7/5
Mussig Pat 7/31
Glenn Esther 10/17
Zerbe Lois 11/21
Peterson Nancy 12/18
The first 38 rows sort, and the latter 14 rows also sort but independent
of the first 38 rows.

"Pete_UK" wrote:

Can you post some examples of what you have and where the data is not
sorting correctly?

If the birthdays have been entered as dd/mm, then Excel will take this
as meaning a date and will assume the current year. Formatting might
allow you to hide the year, but it is still there if those are correct
Excel dates. You might have some dates of birth with the correct year,
but as those are hidden then it might appear that the dates have been
sorted incorrectly. Format the cells as dd/mm/yyyy (temporarily) to
see what is really in those cells.

Another problem might be that you have entered dd/mm as a text value,
and these will sort in a different way. They will be unaffected by
changing the format to dd/mm/yy.

Hope this helps.

Pete

On Jan 12, 6:33 pm, 4most wrote:
Xcel 2003. Similar to an earlier thread. I have verified that all cells are
formatted as numbers, dates 00/00. Some of the items in the column will
sort, but some will not. I have cleared the print area; I have Unhid the
superfluous columns, thinking I might have to sort the birthdates and THEN
hide. No joy.

I don't comprehend
=isnumber(a1)
Now you know the extent of my ability.

or
=text(a1,"mm/dd")



"Dave Peterson" wrote:

Checking the format of the cell isn't enough. You have to check to see if the
value in the cell is numeric:

=isnumber(a1)
is one way.

But if the values are really dates, then excel will use the year if you sort by
this column. Even if you can't see the year in the cell!

You could add another column to the the data range and sort the range by that
helper column.

=text(a1,"mm/dd")

This is just a string (not a date) and doesn't include the year.

4most wrote:

Xcel 2003. Similar to an earlier thread. I have verified that all cells are
formatted as numbers, dates 00/00. Some of the items in the column will
sort, but some will not. I have cleared the print area; I have Unhid the
superfluous columns, thinking I might have to sort the birthdates and THEN
hide. No joy.

--

Dave Peterson

Sorry . . . I should say mm/dd.

"Pete_UK" wrote:

Can you post some examples of what you have and where the data is not
sorting correctly?

If the birthdays have been entered as dd/mm, then Excel will take this
as meaning a date and will assume the current year. Formatting might
allow you to hide the year, but it is still there if those are correct
Excel dates. You might have some dates of birth with the correct year,
but as those are hidden then it might appear that the dates have been
sorted incorrectly. Format the cells as dd/mm/yyyy (temporarily) to
see what is really in those cells.

Another problem might be that you have entered dd/mm as a text value,
and these will sort in a different way. They will be unaffected by
changing the format to dd/mm/yy.

Hope this helps.

Pete

On Jan 12, 6:33 pm, 4most wrote:
Xcel 2003. Similar to an earlier thread. I have verified that all cells are
formatted as numbers, dates 00/00. Some of the items in the column will
sort, but some will not. I have cleared the print area; I have Unhid the
superfluous columns, thinking I might have to sort the birthdates and THEN
hide. No joy.

=isnumber()
is a worksheet function. Just like =sum() or =average().

=isnumber(a1)
will return true if A1 contains a number and will return false if A1 is not a
number.

put a date in A1
and put this in B1
=text(a1,"mm/dd")

You'll see what's returned.


4most wrote:

I don't comprehend
=isnumber(a1)
Now you know the extent of my ability.

or
=text(a1,"mm/dd")

"Dave Peterson" wrote:

Checking the format of the cell isn't enough. You have to check to see if the
value in the cell is numeric:

=isnumber(a1)
is one way.

But if the values are really dates, then excel will use the year if you sort by
this column. Even if you can't see the year in the cell!

You could add another column to the the data range and sort the range by that
helper column.

=text(a1,"mm/dd")

This is just a string (not a date) and doesn't include the year.

4most wrote:

Xcel 2003. Similar to an earlier thread. I have verified that all cells are
formatted as numbers, dates 00/00. Some of the items in the column will
sort, but some will not. I have cleared the print area; I have Unhid the
superfluous columns, thinking I might have to sort the birthdates and THEN
hide. No joy.

--

Dave Peterson


--

Dave Peterson

If that's all you have in the cell, it is text only.

What do you see in the formula bar?

11/3 is not a valid mm/dd date.


Gord Dibben MS Excel MVP

On Mon, 12 Jan 2009 13:43:03 -0800, 4most
wrote:

No yyyy is in the spreadsheet, merely dd/mm.



Nelson Helen 11/3
Cheresnuik Audrey 11/6
Smith Gordon 11/27
Kinsella Joan 12/26
Macon Nina 12/30
Miller Beverly 1/26
Patrick Therese 3/18
Luft Marion 3/29
Hallberg Betty 4/15
Cripe Betty 4/22
Wheat Rosemary 4/25
Broas Jackie 5/18
Hongsermeier Wilma 6/19
Rodgers Arlene 6/26
Sattel Sue 7/5
Mussig Pat 7/31
Glenn Esther 10/17
Zerbe Lois 11/21
Peterson Nancy 12/18
The first 38 rows sort, and the latter 14 rows also sort but independent
of the first 38 rows.

"Pete_UK" wrote:

Can you post some examples of what you have and where the data is not
sorting correctly?

If the birthdays have been entered as dd/mm, then Excel will take this
as meaning a date and will assume the current year. Formatting might
allow you to hide the year, but it is still there if those are correct
Excel dates. You might have some dates of birth with the correct year,
but as those are hidden then it might appear that the dates have been
sorted incorrectly. Format the cells as dd/mm/yyyy (temporarily) to
see what is really in those cells.

Another problem might be that you have entered dd/mm as a text value,
and these will sort in a different way. They will be unaffected by
changing the format to dd/mm/yy.

Hope this helps.

Pete

On Jan 12, 6:33 pm, 4most wrote:
Xcel 2003. Similar to an earlier thread. I have verified that all cells are
formatted as numbers, dates 00/00. Some of the items in the column will
sort, but some will not. I have cleared the print area; I have Unhid the
superfluous columns, thinking I might have to sort the birthdates and THEN
hide. No joy.