Family Historian User Group

I have many Query Result Sets that contain a Date column.
These may be exported using Query Menu > Save Query Result Set As > Comma-Separated CSV File.
The CSV file can be opened with Excel but the Date column is displayed oddly.
See later spreadsheet image for examples.

Some Dates left-justify in column and display in Formula Bar as 7 November 1845.
Other Dates right-justify in column as 09-May-14 and in Formula Bar as 09/05/1914.
A Google search reveals that Excel and other Spreadsheets do not recognise dates before 1 Jan 1900 and treat them as text!!!

Any attempt to Format Cells as Date does not help.
Any attempt to Format Cells as Text results in the right-justified dates becoming large integers!

The only technique that helps, is to force each Query Result Date to be a text string in Excel by prefixing each date with an apostrophe ( ' ).
For example define the Query Column Date as =Text('' ' . %INDI.BIRT.DATE%).

However, in all the above cases it is impossible to Sort the Excel column of Dates into date order.
Therefore, you are forced to accept the sort order supplied by the FH Query.

Example Excel Spreadsheet of Exported Dates:



Maybe the =DayNumber(%date%) Function would supply an integer for the %date% that could act as a sort column.
Unfortunately, it only supplies an integer for a limited subset of FH date formats, where Day & Month & Year are all defined.

Maybe the =Timespan(%base%, %date%, DAYS) Function would suppy a suitable integer for the %date%, where %base% is a fixed date such as DateAt(1,1,1), which is the =DayNumber() base date of 1 Jan 0001.
It does better than =DayNumber() and supplies an integer for all but Null Dates and Date Phrases.
Unfortunately, it miscalculates the DAYS for any date without a Day or Month component.

Excel Spreadsheet of Exported Dates, DayNumber & Timespan:



This has been Sorted on the Timespan column.
Note that the dates between 1845 and 1830 are not sorted correctly due to the =Timespan() miscalculation.

Help!!!!

ID:4785

Have you tried OpenOffice (Free to download)
That seems to except dates fine for before 1901.
As use it myself and can do sort on Dates fine. And Date Column formatted as Date and as 'dd/mm/yyyy'

There is an add in for Excel that I use, that allow dates before 1900.
It can be downloaded from
http://www.j-walk.com/ss/excel/files/xdate.htm

Thank you TimTreeby and DeeJay1 for your advice.
I have tried OpenOffice Calc and was aware of the Excel add-in.

Unfortunately, neither solve the misbehavior.
Yes, they handle dates before 1900, but only if the FH exported date format is a valid spreadsheet date format.

Many FH exported date formats such as '15 December 1989 (calculated)' and 'between 12 June 1908 and December 1919' and 'Q4 1841' are NOT valid spreadsheet dates.
These dates are still left-justified and do not sort into date order!

I have been able to compose a Query Column Expression that does supply an integer that a spreadsheet can use to sort the rows into date order.
However, it is not very intuitive, but here it is:
=Number(((NumberIf(Text(Year(%INDI.BIRT.DATE%)) = '',9999,Year(%INDI.BIRT.DATE%)) * 10000) + (NumberIf(Text(Month(%INDI.BIRT.DATE%)) = '',0,Month(%INDI.BIRT.DATE%)) * 100) + NumberIf(Text(Day(%INDI.BIRT.DATE%)) = '',0,Day(%INDI.BIRT.DATE%))) * NumberIf(%INDI.BIRT.DATE% >= DateAt(1,1,1),1,-1))

That was easy - NOT!!! Can anyone offer a simpler solution please???