Using Spreadsheets for parish marriages

[Hammerman]

Weird. I'm still using Microsoft 2007.

Doubt they would of stopped that facility.

[Midase]

After right clicking on Format choose Date. In the locality drop down box choose English UK. Now select the date format required.

[LadyJayne]

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

[Hammerman]

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.

[LadyJayne]

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.

[Hammerman]

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.

[Deirdre784]

Could you have the full date (dd-mm-yyyy) in one column and just the year in a separate one?

[Berlin-Bob]

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

[KGarrad]

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