Re: [LegacyUG] Dates in CSV file

2012-02-28 Thread Paul R. 
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

2012-02-28 Thread M. Brenzel 
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

2012-02-28 Thread Jenny M Benson 
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

2012-02-28 Thread brenzelmf 
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

2012-02-28 Thread Mike Fry 
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

2012-02-28 Thread M. Brenzel 
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

2012-02-28 Thread Alan Pereira 
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

2012-02-27 Thread Hal Antonson 
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
  To: 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
  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

2012-02-27 Thread Jerry 
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