Re: [LegacyUG] Dates in CSV file
Excel doesn't recognize dates prior to 1900 so it will leave those alone. Dates starting with 1900 are converted to a number so you can do 'arithmetic' on them. Thus full dates are converted and partial dates are not. You must have the date formatting in excel set up to show m/d/. Those that aren't recognized as dates aren't formatted. Paul On 2/28/2012 3:07 PM, M. Brenzel wrote: > I won't be able to try it until later tonight. But I can tell you that it > appears to only affect full dates after 1 Dec 1900. Dates in the 20th and > 21st centuries seem to be affected if the full date has been entered. Any > dates prior to that are captured correctly in the CSV file and therefore, > open correctly in Excel. > > For example, 19 Jul 1905 is displayed in Legacy with the BirthD field in > tblIR = 0019071905 and BirthSD = 19050719. The CSV file shows it as > 19-Jul-05 and when opened in Excel, the fx displays it as 7/19/2005. > > Mary > > Sent from my iPad > > On Feb 28, 2012, at 2:02 PM, Jenny M Benson wrote: > >> On 28/02/2012 17:28, brenze...@roadrunner.com wrote: >>> I'm hoping that someone can tell me why some of the dates in Legacy >>> are coming into the CSV file in format "dd-mmm-yy" (1-Sep-13) while >>> others are "dd mmm " (1 Sep 1913). It is from there that they >>> are displayed incorrectly in Excel. >>> >>> All of the dates in Legacy look to be formatted the same. >> I may be quite wrong about this, but I have an idea that Legacy will >> accept dates input in any of several formats, but displays them in the >> format chosen by the user. >> >> If this is the case, I think dates will be exported to the CSV file in >> the format they were entered, not in the format they were displayed. >> >> You could test this by entering a couple of dates in different formats >> and then export them and seeing how they look in Excel. I haven't time >> to do that myself at the moment. >> >> -- >> Jenny M Benson > > > Legacy User Group guidelines: > http://www.LegacyFamilyTree.com/Etiquette.asp > Archived messages after Nov. 21 2009: > http://www.mail-archive.com/legacyusergroup@legacyusers.com/ > Archived messages from old mail server - before Nov. 21 2009: > http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ > 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/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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
Re: [LegacyUG] Dates in CSV file
I won't be able to try it until later tonight. But I can tell you that it appears to only affect full dates after 1 Dec 1900. Dates in the 20th and 21st centuries seem to be affected if the full date has been entered. Any dates prior to that are captured correctly in the CSV file and therefore, open correctly in Excel. For example, 19 Jul 1905 is displayed in Legacy with the BirthD field in tblIR = 0019071905 and BirthSD = 19050719. The CSV file shows it as 19-Jul-05 and when opened in Excel, the fx displays it as 7/19/2005. Mary Sent from my iPad On Feb 28, 2012, at 2:02 PM, Jenny M Benson wrote: > On 28/02/2012 17:28, brenze...@roadrunner.com wrote: >> I'm hoping that someone can tell me why some of the dates in Legacy >> are coming into the CSV file in format "dd-mmm-yy" (1-Sep-13) while >> others are "dd mmm " (1 Sep 1913). It is from there that they >> are displayed incorrectly in Excel. >> >> All of the dates in Legacy look to be formatted the same. > > I may be quite wrong about this, but I have an idea that Legacy will > accept dates input in any of several formats, but displays them in the > format chosen by the user. > > If this is the case, I think dates will be exported to the CSV file in > the format they were entered, not in the format they were displayed. > > You could test this by entering a couple of dates in different formats > and then export them and seeing how they look in Excel. I haven't time > to do that myself at the moment. > > -- > Jenny M Benson Legacy User Group guidelines: http://www.LegacyFamilyTree.com/Etiquette.asp Archived messages after Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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
Re: [LegacyUG] Dates in CSV file
On 28/02/2012 17:28, brenze...@roadrunner.com wrote: > I'm hoping that someone can tell me why some of the dates in Legacy > are coming into the CSV file in format "dd-mmm-yy" (1-Sep-13) while > others are "dd mmm " (1 Sep 1913). It is from there that they > are displayed incorrectly in Excel. > > All of the dates in Legacy look to be formatted the same. I may be quite wrong about this, but I have an idea that Legacy will accept dates input in any of several formats, but displays them in the format chosen by the user. If this is the case, I think dates will be exported to the CSV file in the format they were entered, not in the format they were displayed. You could test this by entering a couple of dates in different formats and then export them and seeing how they look in Excel. I haven't time to do that myself at the moment. -- Jenny M Benson Legacy User Group guidelines: http://www.LegacyFamilyTree.com/Etiquette.asp Archived messages after Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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
Re: [LegacyUG] Dates in CSV file
I'm hoping that someone can tell me why some of the dates in Legacy are coming into the CSV file in format "dd-mmm-yy" (1-Sep-13) while others are "dd mmm " (1 Sep 1913). It is from there that they are displayed incorrectly in Excel. All of the dates in Legacy look to be formatted the same. Thanks. Mary Mike Fry wrote: > On 2012/02/28 13:49, M. Brenzel wrote: > > > I have Excel 2007. Re-formatting the entire column is not an option, as > > dates that are year only (for example, 1965) get messed up. > > Legacy dates are not always 'true' dates. It would be better if your > spreadsheets treated Legacy dates as simple, plain text. > > -- > Regards, > Mike Fry > Johannesburg > > > > Legacy User Group guidelines: > http://www.LegacyFamilyTree.com/Etiquette.asp > Archived messages after Nov. 21 2009: > http://www.mail-archive.com/legacyusergroup@legacyusers.com/ > Archived messages from old mail server - before Nov. 21 2009: > http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ > 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/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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
Re: [LegacyUG] Dates in CSV file
On 2012/02/28 13:49, M. Brenzel wrote: > I have Excel 2007. Re-formatting the entire column is not an option, as > dates that are year only (for example, 1965) get messed up. Legacy dates are not always 'true' dates. It would be better if your spreadsheets treated Legacy dates as simple, plain text. -- Regards, Mike Fry Johannesburg Legacy User Group guidelines: http://www.LegacyFamilyTree.com/Etiquette.asp Archived messages after Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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
RE: [LegacyUG] Dates in CSV file
I have Excel 2007. Re-formatting the entire column is not an option, as dates that are year only (for example, 1965) get messed up. Mary -Original Message- From: Jerry [mailto:jerrysemailgro...@gmail.com] Sent: Monday, February 27, 2012 10:00 PM To: LegacyUserGroup@LegacyUsers.com Subject: Re: [LegacyUG] Dates in CSV file What version of Excel are you using? The newest versions will alow you to format dates with 4 digits for the year. Highlight or select the entire column, go to Format cells, select the number tab, select Date and then select the specific format you want. I hope this helps. Jerry "M. Brenzel" wrote: >I have created a CSV file from the Name List containing Surname/Given >Name, Living Indicator, Birth Date, and Death Date. When I look at the >file in Notepad, the dates look good. But when I open it in Excel, >some of the dates are formatted differently than most of the others. > >For example, the date 1 Sep 1913 in Legacy looks the same in Notepad >but in Excel, it is displayed as 1-Sep-13. When I look at the table in >Access (tblIR), the dates look different than those that are correct. > >Does anyone have an idea why the dates are "messed up" for some people >but not others? And is there an easy way to fix them? > >Thanks, >Mary > Legacy User Group guidelines: http://www.LegacyFamilyTree.com/Etiquette.asp Archived messages after Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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
RE: [LegacyUG] Dates in CSV file
Mary, the dates are not wrong they are just different formats. Excel applies an intelligent automatic formatting of dates which depends on the input formatting and the century part of the date. Hal Antonson has already replied on how to overcome this. The intelligence is laughable if you consider the date you mentioned Just type into excel in 2 dirrent cells 1 sep 1913 1 sep 2013 The result is the same 1-sep-13 Although in the fx display above the columns the date is identified correctly Alan Pereira -Original Message- From: M. Brenzel [mailto:brenze...@roadrunner.com] Sent: 28 February 2012 02:14 To: LegacyUserGroup@LegacyUsers.com Subject: [LegacyUG] Dates in CSV file I have created a CSV file from the Name List containing Surname/Given Name, Living Indicator, Birth Date, and Death Date. When I look at the file in Notepad, the dates look good. But when I open it in Excel, some of the dates are formatted differently than most of the others. For example, the date 1 Sep 1913 in Legacy looks the same in Notepad but in Excel, it is displayed as 1-Sep-13. When I look at the table in Access (tblIR), the dates look different than those that are correct. Does anyone have an idea why the dates are "messed up" for some people but not others? And is there an easy way to fix them? Thanks, Mary Legacy User Group guidelines: http://www.LegacyFamilyTree.com/Etiquette.asp Archived messages after Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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
Re: [LegacyUG] Dates in CSV file
Mary, Highlight the Excel column then format as custom 'd mmm ' (without the quotes). From the Home menu, click right adjust so they line up on right. Things should look good. If you need to do arithmetic on the dates, remember that dates before 1900 are really just text instead of numbers. If you need to work with those early ones, look at installing Xdate as an Excel addin. - Original Message - From: M. Brenzel<mailto:brenze...@roadrunner.com> To: LegacyUserGroup@LegacyUsers.com<mailto:LegacyUserGroup@LegacyUsers.com> Sent: Monday, February 27, 2012 6:14 PM Subject: [LegacyUG] Dates in CSV file I have created a CSV file from the Name List containing Surname/Given Name, Living Indicator, Birth Date, and Death Date. When I look at the file in Notepad, the dates look good. But when I open it in Excel, some of the dates are formatted differently than most of the others. For example, the date 1 Sep 1913 in Legacy looks the same in Notepad but in Excel, it is displayed as 1-Sep-13. When I look at the table in Access (tblIR), the dates look different than those that are correct. Does anyone have an idea why the dates are "messed up" for some people but not others? And is there an easy way to fix them? Thanks, Mary Legacy User Group guidelines: http://www.LegacyFamilyTree.com/Etiquette.asp<http://www.legacyfamilytree.com/Etiquette.asp> Archived messages after Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyusers.com/<http://www.mail-archive.com/legacyusergroup@legacyusers.com/> Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/<http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/> Online technical support: http://www.LegacyFamilyTree.com/Help.asp<http://www.legacyfamilytree.com/Help.asp> Follow Legacy on Facebook (http://www.facebook.com/LegacyFamilyTree<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<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/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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
Re: [LegacyUG] Dates in CSV file
What version of Excel are you using? The newest versions will alow you to format dates with 4 digits for the year. Highlight or select the entire column, go to Format cells, select the number tab, select Date and then select the specific format you want. I hope this helps. Jerry "M. Brenzel" wrote: >I have created a CSV file from the Name List containing Surname/Given Name, >Living Indicator, Birth Date, and Death Date. When I look at the file in >Notepad, the dates look good. But when I open it in Excel, some of the >dates are formatted differently than most of the others. > >For example, the date 1 Sep 1913 in Legacy looks the same in Notepad but in >Excel, it is displayed as 1-Sep-13. When I look at the table in Access >(tblIR), the dates look different than those that are correct. > >Does anyone have an idea why the dates are "messed up" for some people but >not others? And is there an easy way to fix them? > >Thanks, >Mary > > > > > > > > >Legacy User Group guidelines: >http://www.LegacyFamilyTree.com/Etiquette.asp >Archived messages after Nov. 21 2009: >http://www.mail-archive.com/legacyusergroup@legacyusers.com/ >Archived messages from old mail server - before Nov. 21 2009: >http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ >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/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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
[LegacyUG] Dates in CSV file
I have created a CSV file from the Name List containing Surname/Given Name, Living Indicator, Birth Date, and Death Date. When I look at the file in Notepad, the dates look good. But when I open it in Excel, some of the dates are formatted differently than most of the others. For example, the date 1 Sep 1913 in Legacy looks the same in Notepad but in Excel, it is displayed as 1-Sep-13. When I look at the table in Access (tblIR), the dates look different than those that are correct. Does anyone have an idea why the dates are "messed up" for some people but not others? And is there an easy way to fix them? Thanks, Mary Legacy User Group guidelines: http://www.LegacyFamilyTree.com/Etiquette.asp Archived messages after Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyusers.com/ Archived messages from old mail server - before Nov. 21 2009: http://www.mail-archive.com/legacyusergroup@legacyfamilytree.com/ 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
