RootsChat.Com
General => Technical Help => Family History Programs, Tree Organisation, Presentation => Topic started by: LadyJayne on Tuesday 26 December 17 21:21 GMT (UK)
-
Hello listers, hope everyone is enjoying :D boxing day. Lovely to have a bit of spare time off work so it's a great opportunity for me to think about how to go about using spreadsheets for parish marriages and the best way to input them. I've been using excel for general birth marriages and death indexes but trying to type in the marriage information is proving a bit difficult as keeping the bride and groom together makes for a very long vertical row which on a small screen is not ideal and I was just looking for other users ideas on this.
-
Hi LadyJane,
not a direct answer, but you may find some useful tips and hints here:
RootsChat Topics: Organising and Presenting your Family History
http://www.rootschat.com/forum/index.php?topic=158638.0
in particular, ideas on using spreadsheets for collecting information.
Topic: Spreadsheets help track Surnames
http://www.rootschat.com/forum/index.php/topic,31779.15.html
and also
Topic: Not realy a tec problem,but about excel
http://www.rootschat.com/forum/index.php/topic,199033.0.html
Good luck,
Bob
-
Thanks Bob,
I did find the topic about Excel very useful, esp the on how to word-wrap. The problem I now have is that for some reason I'm having problems with the way it handles dates. I have the day and month in one column and no matter how much I try and format this it's defaulting to 01/06/2017! when it should just be 01 Jun. I have a separate column for the year. :-\
-
Highlight the column you want for day-month in the top line of your sheet i.e. 'C'
Right click and go 'Format cells' then the 'number' tag
Choose 'Custom' then scroll down till you see 'dd-mmm' and pick it.
Once you have all the months enterered you only have to type say '2 [space] F' to get 02-Feb in the column.
If you 'freeze' the top line (the column descriptions you have entered) and then select all the columns and 'custom sort' you can put everything in order by the columns you want ie. Sort by Year, then date then parish etc. Don't forget though when sorting the whole sheet you have selected all columns and tick the 'My data has headers' box (the frozen description line). If you forget a column all the lines will fail to line up and you work will be wasted. Perhaps get in the habit of saving a copy every so often that you can go back to as it is easily done!
-
Also if you already have a column with dates in (and years) make another column along side it and copy the whole column into it. You can then easily work down the list entering day and month
-
Here's an image of my marriage tab. With so many columns you can sort by any order you like.
-
Highlight the column you want for day-month in the top line of your sheet i.e. 'C'
Right click and go 'Format cells' then the 'number' tag
Choose 'Custom' then scroll down till you see 'dd-mmm' and pick it.
Once you have all the months enterered you only have to type say '2 [space] F' to get 02-Feb in the column.
Hi Hammerman, This didn't work for me, when I type in 02 Feb in the column then tab out of it I get 02/02/2017 :-\
-
Does it work if you only format one cell?
-
No, just tried it and no joy, exactly the same. I'm using Microsoft office 2013
-
Weird. I'm still using Microsoft 2007.
Doubt they would of stopped that facility.
-
After right clicking on Format choose Date. In the locality drop down box choose English UK. Now select the date format required.
-
Hi Midase,
Tried this, if I type in say, 02 Jun 1866 it's fine, but if I put in 01 Jan 1900 I get 01-Jan-00 Likewise if I input 15 Oct 1903 I get 15 October 1903. Maybe I should use three columns.
Many Thanks
-
I went with the separate columns. It don't think it likes years before 1900. Separate columns for years/date/month in number form. The month in number for is for dates before 1752 when the new year date changed, so December was month 10, January month 11, etc.
-
Hi Hammerman
Well............after a lot of head scratching I've now got two columns, the first one has the day and month (text format)Which is fine, although after a number sort it's not sorting properly in the year field. :'(
-
Well done with the perseverance. I can only suggest with the sorting, make sure it's set to sort in that column lowest to highest. Have you frozen the top line? Try sorting again selecting all the rows from the bottom of the sheet and stop at row 2. Make sure the box for 'headers' is ticked/unticked accordingly.
Have you tried sorting years then the date column? If you have 2 in the same year does it sort the date column correctly. When I enter dates I get a dash between the number and month unlike in your screen grab.
-
Could you have the full date (dd-mm-yyyy) in one column and just the year in a separate one?
-
I often use 2 columns:
I enter the date in one column, as "yyyy-mm-dd".
If I only know the year (example 1903) then I enter 1903-00-00
If I also know the month (example october) I enter 1903-10-00
And if I know the day, then I can enter the complete date.
The second column is "freestyle" text ( 1900; August 1901; 3.10.1773; 3rd Q, 1917, etc ).
Then I can sort chronologically using the first column, and print the date using the second column.
Bob
ps.
I've now added this topic to the list ...
RootsChat Topics: Organising and Presenting your Family History
http://www.rootschat.com/forum/index.php?topic=158638.0
-
Excel doesn't believe any dates existed before 1st January 1900! :-\
It's all to do with the way dates are stored internally; as a serial number (1 = 1 Jan 1900; 2 = 2 Jan 1900; etc).
A google search will throw up some tools people have written to overcome the problem:
http://www.exceluser.com/formulas/earlydates.htm
http://spreadsheetpage.com/index.php/tip/working_with_pre_1900_dates/
https://www.pcmag.com/article2/0,2817,2114548,00.asp
-
Found some time to have another go today, for the Column's B and C I have formated it to be General. In the D Column for date I have Text (this does away with the dash) and for Column E (Year) I have Number formats.
I then tried sorting on the year column including the header, sort smallest to largest, this gave me a sort warning asking if I want to expand the selection option which I have ticked and it seems to have worked :D :D
If I typed in just 01 Jul in the Date Column for instance, it would display as 1 Jul but as I wanted 01 Jul I used an apostrophe thus: '01 Jul
-
Excel doesn't believe any dates existed before 1st January 1900! :-\
It's all to do with the way dates are stored internally; as a serial number (1 = 1 Jan 1900; 2 = 2 Jan 1900; etc).
A google search will throw up some tools people have written to overcome the problem:
http://www.exceluser.com/formulas/earlydates.htm
http://spreadsheetpage.com/index.php/tip/working_with_pre_1900_dates/
https://www.pcmag.com/article2/0,2817,2114548,00.asp
Or you could upgrade to LibreOffice which recognises dates back to year one ;)
-
No reason why you can't have more than one date field:
For instance you could have (using your example) column F as
Sorting date
1566-02-14
1766-12-05
1799-12-03
1850-07-01
1866-01-13
1950-03-19
1954-09-22
1966-12-22
1983-05-26
etc.
Then you can sort by name, year, sorting-date", or any other column (place, church, etc), depending on which connections you are trying to find.
Bob
-
Or you could upgrade to LibreOffice which recognises dates back to year one ;)
OOh I didn't know that! ;D I've been looking at LibreOffice because of the Database option
-
Excel doesn't believe any dates existed before 1st January 1900! :-\
It's all to do with the way dates are stored internally; as a serial number (1 = 1 Jan 1900; 2 = 2 Jan 1900; etc).
A google search will throw up some tools people have written to overcome the problem:
http://www.exceluser.com/formulas/earlydates.htm
http://spreadsheetpage.com/index.php/tip/working_with_pre_1900_dates/
https://www.pcmag.com/article2/0,2817,2114548,00.asp
Or you could upgrade to LibreOffice which recognises dates back to year one ;)
Libreoffice Help says the base date has to be one of:
30 Dec 1899 (the default)
01 Jan 1900 (as per StarCalc and Excel)
01 Jan 1904 (as per Apple software)
https://help.libreoffice.org/Calc/Date_and_Time_Functions
-
Libreoffice Help says the base date has to be one of:
30 Dec 1899 (the default)
01 Jan 1900 (as per StarCalc and Excel)
01 Jan 1904 (as per Apple software)
https://help.libreoffice.org/Calc/Date_and_Time_Functions
Yes, if you import the Excel file you may need to change the base date - if all the dates are out by 2 days ;)
And if you you use some obscure Excel function it may not have the same effect, but generally everything will work the same - well, it has for me so far.
-
If I typed in just 01 Jul in the Date Column for instance, it would display as 1 Jul but as I wanted 01 Jul I used an apostrophe thus: '01 Jul
Using an apostrophe means the contents will be accepted as text rather than a date which means searching and sorting may give you funny results.
If you want a date to appear with a leading zero, then you need to enter it as a date and then alter the cell/column format to dd-mmm.
-
It just occured to me when I format the cells to be custom dates as dd/mmm it reads 22-06 which is fine, but in the formula bar at the top I get 22/06/2017 Is this right? I wondered if there was some settings in the Excel Options that need to be changed. :o
-
Could you have the full date (dd-mm-yyyy) in one column and just the year in a separate one?
This has worked well for me, long date format and the year in a separate column. This is what I shall do. Thanks Deirdre :D
-
It just occured to me when I format the cells to be custom dates as dd/mmm it reads 22-06 which is fine, but in the formula bar at the top I get 22/06/2017 Is this right? I wondered if there was some settings in the Excel Options that need to be changed. :o
That's correct.
Excel assumes you intended the current year, if you don't enter one.
You can't control how Excel stores it's dates, but you can control how they are displayed.
-
It just occured to me when I format the cells to be custom dates as dd/mmm it reads 22-06 which is fine, but in the formula bar at the top I get 22/06/2017 Is this right? I wondered if there was some settings in the Excel Options that need to be changed. :o
If you format as dd/mmm it should display as 22/Jun, to get 22-06 you would format as dd-mm . . . I think :-\
If the cell is formatted as a date, whatever the display style, you need to enter the year or it assumes the current year. In my screenshot posted earlier all the cells were formatted as dates but the display template was different for each column.
Mike.
-
Excel & Dates: Dates are stored in Excel as a number, starting with 1 on 1st Jan 1900. The date is then displayed (not stored) in whichever way you want. If you enter a pre-1900 date then that is simply a piece of text with no numerical value.
Lets say you have a series of pre-1900 dates in column B of your spreadsheet in the form dd-mmm-yyyy starting at Row 2, then you can extract the year by typing, into a new column "Year", the formula "=right(B2,4)" which grabs the rightmost four characters of B2. That formula can be copied downwards to the bottom of the sheet.
For post-1900 dates then the formula would be "=Year(B2)". You can then sort your spreadsheet by Year.
Other Excel date conventions: If I type "1/3" into Excel then the text "01-Mar" appears on my spreadsheet - it assumes that I have typed a day/month. But it is actually the number 43160.00 which is stored - i.e. 1st Mar 2018, 43160 days from the beginning of the 20th Century. If I type "=Now()" I get 31-03-18 21:11 displayed, but 43190.8827 is actually stored, with the time stored as a decimal fraction of the 24-hr day.
The Calendar
Originally the Christian calendar year started on 25th March. Scotland changed to 1st January in 1600, so January, February and most of March 1599 did not exist. England followed suit in 1752, so from 1707 to 1752 the unified country had two different calendars - February 1720 in England would be the same as February 1721 in Scotland, but April 1720 means the same in both. In original records you might find "Old Style" or "New Style" in date references. The famous "11 days" lost from the calendar occurred in both countries in 1752, marking the full adoption of the Gregorian Calendar with its rules for leap years and which most of Europe had been using since 1582.