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.

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:


U.S. citizens may prefer the format in row 4, which is obtained using the formula:


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 accessedcount times since July 1, 2004.

    Top                                                                               Back