Using Spreadsheets for parish marriages

[KGarrad]

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.

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.

[mike175]

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.

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.

[Stuart P]

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.