* CSV date issues in spreadsheets

Importing from or exporting to another genealogy program. This is the place to ask.
Post Reply
avatar
bbottomley
Platinum
Posts: 32
Joined: 23 Aug 2015 19:39
Family Historian: V6

CSV date issues in spreadsheets

Post by bbottomley » 26 Jul 2020 13:58

I'm attempting to use data exported from from FH in csv format to populate a spreadsheet allowing me to manupulate the dates to link up with some external data.

Having successfully saved an ancestor's timeline to a csv file I expected to be able to open it in either LibreOffice or MS Excel and use some of the date functions to link up with a range of dates from another source I'm developing for my own amusement. However the csv formatted dates do not seem to exhibit the normal properties I would have expected and create some very strange values.
By way of example, below is a csv file opened in LibreOffice
Capture2.JPG
Capture2.JPG (103.11 KiB) Viewed 1083 times
This appears as it does in FH query so no problem there, however even the simplest operation on a date creates very unexpected result. Even a simple copy inserting of cell values creates surprises as in cell D2 which has the simple formula of D2 = A2
Capture4.JPG
Capture4.JPG (143.68 KiB) Viewed 1083 times
So as can be seen 1804 become 10 Dec 1904 and similar anomalies can be seen further down the column, but some of the dates are copied faithfully. As this phenomenon seems common to both LibreOffice Calc and MS Excel I am beginning to suspect it is due to a peculiarity of how dates are formatted/stored in FH.

Does anyone have any ideas how these little gremlins can be tamed?
As always, many thanks in advance.
Brian

User avatar
tatewise
Megastar
Posts: 18642
Joined: 25 May 2010 11:00
Family Historian: V6.2
Location: Torbay, Devon, UK
Contact:

Re: CSV date issues in spreadsheets

Post by tatewise » 26 Jul 2020 14:25

This is a known spreadsheet problem.
In Excel, all Date fields are effectively a serial number derived from an origin date of December 30, 1899.
So pre-1900 dates cannot be represented easily because negative serial numbers are not allowed.

To explain the anomalies, 10 Dec 1904 is 1804 days after December 30, 1899.

If you Google search for Excel dates before 1900 there are various workarounds on offer including VBA from MS.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry

avatar
bbottomley
Platinum
Posts: 32
Joined: 23 Aug 2015 19:39
Family Historian: V6

Re: CSV date issues in spreadsheets

Post by bbottomley » 26 Jul 2020 15:07

Ah, thanks Mike.

If things were easy they wouldn't be worth doing!

Regards
Brian

User avatar
AdrianBruce
Megastar
Posts: 953
Joined: 09 Aug 2003 21:02
Family Historian: V6.2
Location: South Cheshire
Contact:

Re: CSV date issues in spreadsheets

Post by AdrianBruce » 26 Jul 2020 16:33

To add spice to it, I suspect that the first column won't even be recognised as a column of dates without some sort of intervention. Characters like "(est)" will be recognised only as text, so the cell will be treated as text. Remember, a spreadsheet, like all software, is totally and utterly pedantic.

If I recall correctly, I end up splitting the year (at least) off in Excel when entering parish register etc dates that I want to manipulate - presumably for exactly the reason that Mike alludes to.
Adrian

avatar
bbottomley
Platinum
Posts: 32
Joined: 23 Aug 2015 19:39
Family Historian: V6

Re: CSV date issues in spreadsheets

Post by bbottomley » 26 Jul 2020 20:10

Yes, I'm starting to navigate my way round the problem and learning new skills along the way!
Why on earth was 1900 ever seen as the start of all recorded time??

Cheers
Brian

User avatar
Jane
Site Admin
Posts: 8003
Joined: 01 Nov 2002 15:00
Family Historian: V6.2
Location: Somerset, England
Contact:

Re: CSV date issues in spreadsheets

Post by Jane » 27 Jul 2020 06:23

What I have done previously is to include Year, Month and Day as separate columns in the query which makes it easier to make comparisons etc in Excel
Jane
My Family History : My Photography "Knowledge is knowing that a tomato is a fruit. Wisdom is not putting it in a fruit salad."

avatar
Mark1834
Famous
Posts: 167
Joined: 27 Oct 2017 19:33
Family Historian: V6.2
Location: South Cheshire

Re: CSV date issues in spreadsheets

Post by Mark1834 » 27 Jul 2020 08:19

Another trick I have used in the past to represent a date as a single integer is year + month x 16 + day x 32. That makes it readily accessible to bit-masking routines to extract individual fields.

Time started in 1900 because Excel and its various clones are designed as business software, and way back when standards were set nobody anticipated using it for historical data processing.
Mark Draper

avatar
E Wilcock
Megastar
Posts: 990
Joined: 11 Oct 2014 07:59
Family Historian: V6.1
Location: London
Contact:

Re: CSV date issues in spreadsheets

Post by E Wilcock » 28 Jul 2020 14:22

Dates have always been a great problem for me, in using MS Access or Excell.
Historians sometimes find for example that a letter was written during the summer of 1934. Bibliography software allows for seasonal dating and has more flexibility. For a long time I kept one of my historical databases in Procite.

Post Reply