* CSV date issues in spreadsheets

[bbottomley]

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 (103.11 KiB) Viewed 2866 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 (143.68 KiB) Viewed 2866 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

[tatewise]

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.

[bbottomley]

Ah, thanks Mike.

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

Regards
Brian

[AdrianBruce]

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.

[bbottomley]

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

[Jane]

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

[Mark1834]

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.

[E Wilcock]

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.