* Perhaps an Event with 2 date fields (plotting immigration)

Questions regarding use of any Version of Family Historian. Please ensure you have set your Version of Family Historian in your Profile
Post Reply
avatar
tparkhill
Gold
Posts: 22
Joined: 08 Dec 2020 17:46
Family Historian: V7

Perhaps an Event with 2 date fields (plotting immigration)

Post by tparkhill » 05 Aug 2022 23:23

I want to clean up and enter immigration information for many ancestors and then export to plot the data. I don’t have immigration dates for most ancestors. However, I can narrow down a range of years. E.g.
  • Immigration must be after they were born in England and before they died in America,
    If a mother immigrated and baby was born in US, it must be before birth of baby,
    While I am making assumptions, if married in America and baby born in America, it is likely after marriage date, etc.
My goal is to have 2 immigration dates “Earliest Likely” Latest Likely”
Then when I export to Excel or other plotting software, I would plot a horizontal bar with earliest and latest date for each person. I plan to color code bars by country they came from. Then If I plot all this vs. time on x-axis, I have a timeline showing when surges of family immigration happened. I would then add events showing what was going on in their country of origin (etc.) so I could see patterns and drivers of immigration.
I think this will be very interesting for my kids/etc.
To do this, I need Excel file with 2 date columns.
I just tried exporting from standard “Immigration” field.
That field does allow Between 1620 and 1630, for example. But there are currently many date types that come out. I Parse by comma delimited. I can’t parse by space, as too many date formats.
I was thinking I could Copy “Immigration” and add a 2nd date field, so one is earliest date and one is latest date. But the Edit does not seem to allow a second date field.
I guess I can just go through all the records, probably a hundred, and do more of this manually. But was wondering if anyone else has tried having an event with 2 dates?
Any thoughts?
Thanks
Trent

avatar
Mark1834
Megastar
Posts: 1531
Joined: 27 Oct 2017 19:33
Family Historian: V7
Location: South Cheshire, UK

Re: Perhaps an Event with 2 date fields (plotting immigration)

Post by Mark1834 » 06 Aug 2022 07:10

I think the option that gives most flexibility is to invest the time to correct all your immigration dates to standard format (i.e. "between x and y"). Export from FH is then straightforward and predictable in its format. I'm not sure if a query could tabulate earliest and latest dates of a range separately, but if not, it would be easy enough to do after export, as they would all be the same format.

Be aware that many general software applications, including Excel, do not deal with dates earlier than the 20th century very well, but if you are only plotting years, that is not a constaint.
Mark Draper

avatar
Gowermick
Megastar
Posts: 1352
Joined: 13 Oct 2015 07:22
Family Historian: V7
Location: Swansea

Re: Perhaps an Event with 2 date fields (plotting immigration)

Post by Gowermick » 06 Aug 2022 08:28

Mark1834 wrote:
06 Aug 2022 07:10

Be aware that many general software applications, including Excel, do not deal with dates earlier than the 20th century very well, but if you are only plotting years, that is not a constaint.
Mark, I think that only applies if you use YY format instead of YYYY. If you put full year in, there should be no problem.

That is why the year 2000 got everyone worried, because some software developers only used 2 digits for year (saving memory, which was expensive) and assumed century was 1900’s, and then, when 2000 arrived, their software would be making wrong assumption. The correction was to check and correct the format of all dates in the software, to ensure they all used YYYY format!
Mike Loney

Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com

User avatar
Jane
Site Admin
Posts: 8354
Joined: 01 Nov 2002 15:00
Family Historian: V7
Location: Somerset, England
Contact:

Re: Perhaps an Event with 2 date fields (plotting immigration)

Post by Jane » 06 Aug 2022 08:31

In a query you can use

Code: Select all

=Year(%INDI.CENS.DATE:XDATE1%)
and

Code: Select all

=Year(%INDI.CENS.DATE:XDATE2%)
To get the years from the first and second date in a range.

P.S The problem with excel is the fact it uses timestamps when computing dates and if you check the Microsoft Documents it specifically states the earliest date for calculations is 1 Jan 1901
Jane
My Family History : My Photography "Knowledge is knowing that a tomato is a fruit. Wisdom is not putting it in a fruit salad."

User avatar
tatewise
Megastar
Posts: 24655
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Perhaps an Event with 2 date fields (plotting immigration)

Post by tatewise » 06 Aug 2022 09:56

I agree with Jane regarding Day/Month/Year dates in Excel. They are not supported earlier than 1 Jan 1901.

However, if you reduce all dates to just the year they are treated as integers in Excel.

Unfortunately, Jane chose to use the Census event in her example instead of Immigration which would be the following Expressions in two Columns and would become comma separated in the exported CSV file.

Code: Select all

=Year(%INDI.IMMI.DATE:XDATE1%)
and

Code: Select all

=Year(%INDI.IMMI.DATE:XDATE2%)
i.e. With a date such as Between Jan 1850 and Feb 1902 the XDATE1 column is 1850 and XDATE2 column is 1902.

If there is only a single date such as 20 Mar 1870 then XDATE1 column is 1870 and XDATE2 column is blank.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

User avatar
davidf
Megastar
Posts: 661
Joined: 17 Jan 2009 19:14
Family Historian: V6.2
Location: UK

Re: Perhaps an Event with 2 date fields (plotting immigration)

Post by davidf » 06 Aug 2022 10:29

If you want to do date calculations prior to 1900, you could always install LibreOffice (free-ware and an Excel "clone" - pre-Ribbon) - which tells me for instance that the "date difference" in days between the 1871 Census and the 1881 Census is 3654 days
Screenshot from 2022-08-06 11-27-22.png
datedif in Libre Office
Screenshot from 2022-08-06 11-27-22.png (40.58 KiB) Viewed 234 times
David
Running FH 6.2.7. Under Wine on Linux (Lubuntu 18.04 LTS)

avatar
Gowermick
Megastar
Posts: 1352
Joined: 13 Oct 2015 07:22
Family Historian: V7
Location: Swansea

Re: Perhaps an Event with 2 date fields (plotting immigration)

Post by Gowermick » 06 Aug 2022 10:49

Miketate, I am truly astonished that excel still uses such an antiquated system for dates.

Like Mark, I use Libreoffice, (anything but Microsoft :lol: ) which doesn’t suffer the same problem, so I had assumed wrongly that the latest excel used a similar system - my mistake :roll:
Mike Loney

Website http://www.loney.tribalpages.com
http://www.mickloney.tribalpages.com

User avatar
tatewise
Megastar
Posts: 24655
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Perhaps an Event with 2 date fields (plotting immigration)

Post by tatewise » 06 Aug 2022 10:54

Actually, they both use essentially the same mechanism for dates, with 1 Jan 1901 as the base date, but LibreOffice allows earlier dates to have a negative integer days value, and also caters for the Gregorian date change missing 11 days.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

avatar
tparkhill
Gold
Posts: 22
Joined: 08 Dec 2020 17:46
Family Historian: V7

Re: Perhaps an Event with 2 date fields (plotting immigration)

Post by tparkhill » 06 Aug 2022 16:12

Thank you all. This is perfect. I do have to consolidate the many "immigration" fields I have in Events and Attributes. But I tested this on one of them in Events (by creating a Test Person, it appears to be the official intended FH Immigration field) and it seemed to work perfectly.
And actually, it appears that when only one year is given, it puts that year in both columns, which will be handy.

One related question . . .

1) once I add a Col into a query, I see that I can Disable it. But often when I am experimenting, I have a Col I tried but no longer want. And I want to remove the Column from the query to avoid confusion (i.e. I want to delete vs disable). I would have guessed there would be a way to do this vs. starting over, either a reverse arrow between "Fields" and "Columns", or a "Delete" option with the Right Click. Am I missing an obvious way to delete?

Thanks agin.
Trent

User avatar
tatewise
Megastar
Posts: 24655
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Perhaps an Event with 2 date fields (plotting immigration)

Post by tatewise » 06 Aug 2022 16:27

Yes, sorry, you are correct and single dates put the Year in both XDATE1 and XDATE2 :oops:
For further details about Dates see the Family Historian Help page for General Topics + Date Formats
https://www.family-historian.co.uk/help ... rmats.html

To delete a Query Column (or a Row) use the X (Delete) lower left below the list of Columns, just above Heading: and next to the up/down arrows.

TIP: Hit the F1 keyboard button to open the Query Help page https://www.family-historian.co.uk/help ... indow.html, which leads to Help for each tab: General, Columns, Rows, Result Set and they explain all the controls including what "Deletes the currently selected row(s) in the Columns List."
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

avatar
tparkhill
Gold
Posts: 22
Joined: 08 Dec 2020 17:46
Family Historian: V7

Re: Perhaps an Event with 2 date fields (plotting immigration)

Post by tparkhill » 06 Aug 2022 18:09

Thanks again. Yes I had found that help, but apparently did not absorb it all.
That Delete "X' is subtle .
There is a lot to learn, . . . . and perhaps I should have taken up trying to "finish" my dad's genealogy project when I was a little younger and sharper :D
Trent

Post Reply