How do I sort dates in Excel

[Lorene Schertzl]

I am transcribing a cemetery and want to be able to sort by birth and death
date. Right now a have all dates entered as yyyy-mm-dd.eventually this will
be posted on line for geologists. It would be much better if it were in the
format of day, month,year (28-Apr 1893). I have downloaded the xDate add in
for Excel dates before 1900 (hope I installed it correctly). Running Excel
2002

Is there any way to change these thousands of dates to the way that I want
them? I tried reformatting the columns

 

[David McRitchie]

Hi Lorene,
Geologists generally wouldn't be very interested in such recent dates,
you meant genealogists.

I would suggest you stick to form you are currently entering them BUT
make sure that you are entering TEXT and not dates. Format the column
as TEXT. Don't change the format from what you have and do make sure
that you can only enter as text..

If I entered them as you are entering them with a format of General, they would
become dates and you definitely do not want them as Excel
dates as anything before March 1, 1904 would essentially be invalid or
suspect. Excel will not recognize dates before Jan 1, 1900 in any case.

You could use John Walkenbach's extended date routines
which uses VBA instead of Excel -- but I think you are a lot safer using
text dates and also keep in mind that the calendar was changed at different
times in different places. If you used John's subroutines you would
end up having to convert them and unless you were in one location you really
wouldn't know what you had.
http://www.mvps.org/dmcritchie/excel/datetime.htm

Also if you are talking about genealogy a date of 28-Apr 1893 is not going
to look that great to everyone working with the data, but the main thing is
to avoid ambiguity and avoid all unexpected conversions. Once you have
the data in a GEDCOM formatted file there should be no ambiguity and you
should be able to pass the data between various genealogy programs..

 

[Lorene Schertzl]

Hi David,
Thank you for your reply. You're right, I did mean genealogist. I use a
program called Dragon NaturallySpeaking and it doesn't always write things
right, and I don't prove read sometimes. I have about 6000 dates entered.
My problem is, when I put it on line I want it to read as 28 Apr 1893, etc..
Can I copy the whole column and paste it into a text formatted column and it
will convert them? I'm probably asking for a miracle, right? If they're
formatted as text they won't sort by a date anymore will they?

Hi Lorene,
Geologists generally wouldn't be very interested in such recent dates,
you meant genealogists.

I would suggest you stick to form you are currently entering them BUT
make sure that you are entering TEXT and not dates. Format the column
as TEXT. Don't change the format from what you have and do make sure
that you can only enter as text..

If I entered them as you are entering them with a format of General, they would
become dates and you definitely do not want them as Excel
dates as anything before March 1, 1904 would essentially be invalid or
suspect. Excel will not recognize dates before Jan 1, 1900 in any case.

You could use John Walkenbach's extended date routines
which uses VBA instead of Excel -- but I think you are a lot safer using
text dates and also keep in mind that the calendar was changed at different
times in different places. If you used John's subroutines you would
end up having to convert them and unless you were in one location you really
wouldn't know what you had.
http://www.mvps.org/dmcritchie/excel/datetime.htm

Also if you are talking about genealogy a date of 28-Apr 1893 is not going
to look that great to everyone working with the data, but the main thing is
to avoid ambiguity and avoid all unexpected conversions. Once you have
the data in a GEDCOM formatted file there should be no ambiguity and you
should be able to pass the data between various genealogy programs..
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am transcribing a cemetery and want to be able to sort by birth and death
date. Right now a have all dates entered as yyyy-mm-dd.eventually this will
be posted on line for geologists. It would be much better if it were in the
format of day, month,year (28-Apr 1893). I have downloaded the xDate add in
for Excel dates before 1900 (hope I installed it correctly). Running Excel
2002

Is there any way to change these thousands of dates to the way that I want
them? I tried reformatting the columns

 

[David McRitchie]

Hi Lorene,
I already gave you my suggestion, keep entering as text as you are
doing with the year first. Anything else and you will be creating a boatload
of problems for yourself. You cannot have dates before 1900 in Excel by itself.
So you would be setting yourself up for failure and lots of problems.

Hi David,
Thank you for your reply. You're right, I did mean genealogist. I use a
program called Dragon NaturallySpeaking and it doesn't always write things
right, and I don't prove read sometimes. I have about 6000 dates entered.
My problem is, when I put it on line I want it to read as 28 Apr 1893, etc..
Can I copy the whole column and paste it into a text formatted column and it
will convert them? I'm probably asking for a miracle, right? If they're
formatted as text they won't sort by a date anymore will they?

Hi Lorene,
Geologists generally wouldn't be very interested in such recent dates,
you meant genealogists.

I would suggest you stick to form you are currently entering them BUT
make sure that you are entering TEXT and not dates. Format the column
as TEXT. Don't change the format from what you have and do make sure
that you can only enter as text..

If I entered them as you are entering them with a format of General, they would
become dates and you definitely do not want them as Excel
dates as anything before March 1, 1904 would essentially be invalid or
suspect. Excel will not recognize dates before Jan 1, 1900 in any case.

You could use John Walkenbach's extended date routines
which uses VBA instead of Excel -- but I think you are a lot safer using
text dates and also keep in mind that the calendar was changed at different
times in different places. If you used John's subroutines you would
end up having to convert them and unless you were in one location you really
wouldn't know what you had.
http://www.mvps.org/dmcritchie/excel/datetime.htm

Also if you are talking about genealogy a date of 28-Apr 1893 is not going
to look that great to everyone working with the data, but the main thing is
to avoid ambiguity and avoid all unexpected conversions. Once you have
the data in a GEDCOM formatted file there should be no ambiguity and you
should be able to pass the data between various genealogy programs..
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am transcribing a cemetery and want to be able to sort by birth and death
date. Right now a have all dates entered as yyyy-mm-dd.eventually this will
be posted on line for geologists. It would be much better if it were in the
format of day, month,year (28-Apr 1893). I have downloaded the xDate add in
for Excel dates before 1900 (hope I installed it correctly). Running Excel
2002

Is there any way to change these thousands of dates to the way that I want
them? I tried reformatting the columns