Using Microsoft Excel with Pre 1900 Dates.
Microsoft excel does not recognise dates before 1900, making it difficult to use for genealogists. There are many ways that dates can be manipulated within Excel, this page suggests several possible ways, all variations on the same theme.
My favourite way is the following, and will work as far back as Jan 1 1000.
Enter all dates as a single number in the format YYYYMMDD e.g May 1
1951 becomes 19510501, December 25 1737 becomes 17371225. This is the
column that will be used to sort your data, and if necessary can be hidden after
the sort is complete so that it is not visible to the user.
| A | B | C | D | E | |
| 1 | Surname | Forename | Birth Entry | Birth Display | BirthPlace |
| 2 | Bevan | Dai | 18510501 | 01 May 1851 | Neath |
| 3 | Bevan | Dai | 18510501 | 01/05/1851 | Neath |
| 4 | Bevan | Dai | 18510501 | 05/01/1851 | Neath |
The Date in cell D2 (01 May 1951) is generated by typing the following formula into the cell:
=RIGHT(C2,2)&" "&MID("JanFebMarAprMayJunJulAugSepOctNovDec",(3*MID(C2,5,2)-2),3)&"
"&LEFT(C2,4)
Note: This formula may have wrapped in your browser window, it should
be copied as a single continuous line. The characters shown in red (i.e.
C2) are the cell reference for your Date Entry,
and can be changed as appropriate for your worksheet. There is a space between
the quotes.
The Date in cell D3 (01/05/1951) is generated using a simpler formula:
=RIGHT(C3,2)&"/"&MID(C3,5,2)&"/"&LEFT(C3,4)
U.S. citizens may prefer the format in row 4, which is obtained using the formula:
=MID(C4,5,2)&"/"&RIGHT(C4,2)&"/"&LEFT(C4,4)
Note It is not necessary to type the formula into every cell of the column. If you click into a cell containing the formula, then position the mouse pointer over the bottom right of the selected cell, the pointer changes to a + . Click and hold the mouse button down while dragging the pointer down the column, and the formula will be copied to all the cells covered.
This page has been accessed
times since July 1, 2004.