According to Microsoft Excel, the World began on January 1st, 1900 - My Ancestors will disagree

In a previous post, I demonstrated the power of using the date format YYYY.MM.DD in Microsoft Explorer to place anything contained in one folder in chronical order.  This is a very powerful tool.  But, when working with Excel the rules change. 

Recently I added several Civil War battle dates to an Excel sheet wishing to sort them in date order.  Nope!  After troubleshooting this, I discovered something interesting.  Excel actually converts dates to serial numbers starting 1/1/1900.  These numbers are then displayed as dates (in various formats) to the user.  The result is no valid date can exist in Excel before 1/1/1900.  That's not good for the genealogist or course. So what to do?

Well, you could use 1900 dates.  Instead of 12/16/1863 one could use 12/16/1963 for the purposes of sorting.  Being 100 years off just seems weird to this genealogist.

Not wishing to have my Civil War dates in the 1900s, I dug deeper and found this fix.  Inasmuch as Excel refuses to recognize DATES before 1/1/1900, let's use simple NUMBERS.  For this workaround the date, 12/16/1863 is represented as 18631216.  Now Excel is forced to take your date; more precisely it doesn't know it is a date. 

Here is how is use this method: For every application where a date is involved I use the YYYY.MM.DD format.  For example, 1941.12.7, or 1961.7.9, or in the case of a Civil War date, 1863.12.16.  This is my standard, but using 12/7/1941, 7/9/1961, or 12/16/1863 works fine too.  Any date format that you like can be applied. 

First I create an Excel document and enter data.  Record #, date, event, etc.  Now, because Excel is not DATE friendly, we need a NUMBER column also.  I label this column as "Sort" and place it to the far right of the Excel page.  For me, the number 18631216 doesn't quickly translate into the date 1863.12.16, or 12/16/1863, or even 16 December 1863 (for you Purist).  But for Excel, in this application (pre 1/1/1900), it is required.

In practical application, I start out with my standard YYYY.MM.DD column and do a Cut/Paste to a far right column when all my work is entered.  I then painfully remove all the "." and add "0" before any month or day less than 10.  The result is a column of eight numbers per cell.  Now I label this column as "Sort" and try not to look at it.  It's ugly in my humble opinion.  Now I sort using the "Sort" column and if asked, Expand to the entire worksheet.  Now my "Dates" are sorted in date order - not under their own power (unfortunately) - and the product works for me.

I've read that OpenOffice's version of the Excel tool does not have this limitation but I have not tested it yet.