I have been working on a geneology spreadsheet for one of my surnames. I can
get the names to go into alphabetical order, but when I want the dates of
birth to ascend or decend, I can get the dob for the 1900's to go in order on
the list but the 1800's will not and the 1800's dates will not change form
ddmmyyyy to mmddyyyy. (I tried to format this data to mmddyyyy, but it will
not change.) I have looked throught all of my info without any results. Any
suggestions.

Excel does not work with dates prior to 1/1/1900. They are converted from
numerical dates to text. John Walkenbach has an Excel Add-In to enable you
to work with these as dates again. Try his site at www.j-walk.com/ss/ to see
if he offers it there...the copy I have was included with a book of his that
I bought.
-KC
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Linda" wrote:

I have been working on a geneology spreadsheet for one of my surnames. I can
get the names to go into alphabetical order, but when I want the dates of
birth to ascend or decend, I can get the dob for the 1900's to go in order on
the list but the 1800's will not and the 1800's dates will not change form
ddmmyyyy to mmddyyyy. (I tried to format this data to mmddyyyy, but it will
not change.) I have looked throught all of my info without any results. Any
suggestions.

Excel doesn't support dates before 1900. Maybe you could enter your data as
text--or use a helper column that can be treated as text:

=text(a1,"yyyymmdd")
and then you can sort your data by that helper column.

As an aside, John Walkenbach has an addin that allows you to do date arithmetic:
http://j-walk.com/ss/excel/files/xdate.htm

You may find it useful for other stuff.

Linda wrote:

I have been working on a geneology spreadsheet for one of my surnames. I can
get the names to go into alphabetical order, but when I want the dates of
birth to ascend or decend, I can get the dob for the 1900's to go in order on
the list but the 1800's will not and the 1800's dates will not change form
ddmmyyyy to mmddyyyy. (I tried to format this data to mmddyyyy, but it will
not change.) I have looked throught all of my info without any results. Any
suggestions.

--

Dave Peterson

Thanks KC and Dave. Your information was very helpful and I did download the
information.

"Dave Peterson" wrote:

Excel doesn't support dates before 1900. Maybe you could enter your data as
text--or use a helper column that can be treated as text:

=text(a1,"yyyymmdd")
and then you can sort your data by that helper column.

As an aside, John Walkenbach has an addin that allows you to do date arithmetic:
http://j-walk.com/ss/excel/files/xdate.htm

You may find it useful for other stuff.

Linda wrote:

I have been working on a geneology spreadsheet for one of my surnames. I can
get the names to go into alphabetical order, but when I want the dates of
birth to ascend or decend, I can get the dob for the 1900's to go in order on
the list but the 1800's will not and the 1800's dates will not change form
ddmmyyyy to mmddyyyy. (I tried to format this data to mmddyyyy, but it will
not change.) I have looked throught all of my info without any results. Any
suggestions.

--

Dave Peterson