Using Spreadsheets for parish marriages

[LadyJayne]

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

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

[mike175]

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 

[Berlin-Bob]

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

[LadyJayne]

Or you could upgrade to LibreOffice which recognises dates back to year one 

OOh I didn't know that! I've been looking at LibreOffice because of the Database option

[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

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

[mike175]

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.

[AngusMcCoatup]

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.

[LadyJayne]

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.

[LadyJayne]

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