Export your dates in the format YYYY-MM-DD. Import to the spreadsheet. Create
an extra column in the spreadsheet and enter the following formula in the first
row:
=IF(ISNUMBER(A1), TEXT(A1,"YYYY-MM-DD"), A1)
Replacing the A1’s with the starting point of your birth dates, then copy it
down the column.
This formula will convert all the post 1900 dates to a text format, and keep
the pre-1900 dates in their existing format which is already text.
You can then sort on this new column. You’ll get a message about mixed text
and numbers, just tell excel to sort them separately and it will do the sort
without converting the recognizable values back into numbers/dates.
From: Paula Ryburn [mailto:[email protected]]
Sent: Sunday, September 07, 2014 4:09 PM
To: [email protected]
Subject: Re: [LegacyUG] Sorting by date of birth
Thanks, Cheryl, for the formatting suggestions. Unfortunately, I have dates
from before 1900 and after 1900, and one cell format works for the former & not
the latter, while another works for the latter & not the former. And I don't
want to re-export & import it all. Disappointing, but will work with what I
have now & test a few things before I do it again... if I remember the Excel
glitch by that time. Short memory these days. ;)
--Paula
_____
From: singhals <[email protected]>
To: [email protected]
Sent: Sunday, August 31, 2014 10:01 AM
Subject: Re: [LegacyUG] Sorting by date of birth
Birth year is in a separate column; mark it in the
spreadsheet with a ' in front to the machine thinks it's an
alpha-num field not a date field. Or, highlight the whole
column and format it as numeric, not date. At least, both
those work on my version of Xcel for me.
Cheryl
Paula Ryburn wrote:
> Hey, Pat, I wanted to share with you what I just
> re-discovered about Excel: It can't handle dates earlier
> than 1900. I knew this, but had forgotten! I was doing just
> what I suggested you do, and I ran into all sorts of date
> format issues in Excel. *sigh* You'd think they could fix
> that, but no. So, I'm guessing sorting your whole database
> by birth DATE could get a bit messy in Excel. Or maybe there
> is a way to set your date format parameter in Legacy before
> you export that would put the dates in the integer format in
> the CSV file...? I may have just crossed over my techie
> limit line. --Paula
>
> ------------------------------------------------------------
> *From:* Paula Ryburn <[email protected]>
> *To:* [email protected]
> *Sent:* Thursday, August 28, 2014 8:05 PM
> *Subject:* Re: [LegacyUG] Sorting by date of birth
>
> Pat, What are you trying to discover or do? Would printing a
> birthday list help? It's called the Calendar Creator now,
> under Other Reports.
> Otherwise, the export to .csv and import into Excel is the
> route I would take. --Paula
>
>
>
> ------------------------------------------------------------
> *From:* Pat Hickin <[email protected]>
> *To:* [email protected]
> *Sent:* Sunday, August 24, 2014 7:45 PM
> *Subject:* [LegacyUG] Sorting by date of birth
>
>
> Is there any way to sort the entire database by, e.g., date
> of birth??
>
> Thanks,
> Pat
Legacy User Group guidelines:
http://www.LegacyFamilyTree.com/Etiquette.asp
Archived messages after Nov. 21 2009:
http://www.mail-archive.com/[email protected]/
Archived messages from old mail server - before Nov. 21 2009:
http://www.mail-archive.com/[email protected]/
Online technical support: http://www.LegacyFamilyTree.com/Help.asp
Follow Legacy on Facebook (http://www.facebook.com/LegacyFamilyTree) and on our
blog (http://news.LegacyFamilyTree.com <http://news.legacyfamilytree.com/> ).
To unsubscribe: http://www.LegacyFamilyTree.com/LegacyLists.asp
Legacy User Group guidelines:
http://www.LegacyFamilyTree.com/Etiquette.asp
Archived messages after Nov. 21 2009:
http://www.mail-archive.com/[email protected]/
Archived messages from old mail server - before Nov. 21 2009:
http://www.mail-archive.com/[email protected]/
Online technical support: http://www.LegacyFamilyTree.com/Help.asp
Follow Legacy on Facebook (http://www.facebook.com/LegacyFamilyTree) and on our
blog (http://news.LegacyFamilyTree.com).
To unsubscribe: http://www.LegacyFamilyTree.com/LegacyLists.asp
Legacy User Group guidelines:
http://www.LegacyFamilyTree.com/Etiquette.asp
Archived messages after Nov. 21 2009:
http://www.mail-archive.com/[email protected]/
Archived messages from old mail server - before Nov. 21 2009:
http://www.mail-archive.com/[email protected]/
Online technical support: http://www.LegacyFamilyTree.com/Help.asp
Follow Legacy on Facebook (http://www.facebook.com/LegacyFamilyTree) and on our
blog (http://news.LegacyFamilyTree.com).
To unsubscribe: http://www.LegacyFamilyTree.com/LegacyLists.asp
