sorting birthdays formatted as month and day (no year involved)

4

4most

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.
 
P

Pete_UK

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
 
D

Dave Peterson

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.
 
4

4most

Pete_UK said:
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
Click to expand...
 
4

4most

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.
 
4

4most

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

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

4most

Sorry . . . I should say mm/dd.

Pete_UK said:
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
Click to expand...
 
D

Dave Peterson

=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.
 
G

Gord Dibben

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
 
Top