Format of dates in Excel from a report

David33 · Post by **David33** » 28 Jul 2005 16:12

When I paste the Results set from a query into an Excel sheet, although the dates in the FH query results set always appear in the form 6 Dec 1872, sometimes the date appears in Excel as 6 Dec 1872, but post-1900 dates, with exactly the same format (for example 6 Dec 1905) in the query results set, appear in Excel as 06/12/05. This only occurs with post-1900 dates. I cannot simply format the column within Excel, as sometimes I know only the year (and have entered, for example, 1905). I have tried retyping the date in Family Historian differently, but it does not seem to make any difference.

The same occurs with baptism and marriage dates if post-1900.

The query is a custom query based on Ancestors, but the date expression is unchanged from the Standard Ancestor Query (INDI.BIRT.DATE:COMPACT), and I'm using Excel from Office 2003, Windows XP Pro, and Regional setting is UK. If I change the expression to INDI.BIRT.DATE:LONG the same thing occurs.

ID:987

David33 · Post by **David33** » 28 Jul 2005 16:22

Shortly after posting this, I realised that the pre-1900 dates are appearing in Excel as text, and not as dates, so I imagine this is a feature of Excel, and not FH. However, if anyone knows how to get round this, I'd like to hear! It will make sorting a bit of a problem!

Post by **nsw** » 28 Jul 2005 21:01

Excel can't interpret pre-1900 dates as dates so you should paste all of the dates in as text I think. Not sure if there is an easier way to do this but if you highlight the cells that you will be pasting the dates into and format them as text then when you paste the dates in they will all be interpreted as text rather than dates.

The problem with using text for dates is that you won't be able to sort them properly or use date formulas on them in Excel. I just googled and found an add-in for Excel which provides some functions for pre-1900 dates if you feel the need to use them:
http://j-walk.com/ss/excel/usertips/tip028.htm

YouTube · Post by **Jane** » 28 Jul 2005 21:37

one possible solution is to put a column for each part of the date or to just use the year. You can then use a formula on the 3 date parts to do days between two dates. It really depends on what you want to achieve

David33 · Post by **David33** » 30 Jul 2005 11:16

Thanks very much for both of these replies. I realise that I haven't discovered this feature of Excel before because I've only ever used pre-1900 years alone, as a number, not a full date! In fact, as all I want to do is create a checklist, one column with the year as number (for sorting) and another with the full date as text will do the trick. I've downloaded the xdate functions, as I'm sure they will come in useful, so thanks for that as well.

David

buxton · Post by **buxton** » 26 Aug 2005 10:07

Within the process of your excel form to attain the correct format you have to format the cells.

Use the drop down menu by clicking on the right hand mouse button, first highlighting the cell which require this action.

By trial and error by picking the category which best suits, eg Date, number, or text. etc

ChrisBowyer · Post by **ChrisBowyer** » 25 Jan 2006 16:40

The problem is Excel not understanding dates before 1900. I've used Excel to manipulate genealogy data and always resorted to 2 columns, one for the year (as a number) and one for the date displayed as day and month (but actually entered as a date in this year). then you can sort the information using both columns.

Family Historian User Group