Pre-1900 dates in Excel?

July 19, 2006 11:25 AM

Is there any way to make Microsoft Excel recognize dates before 1900?

I'm working on a project where I'm creating a long list of names and dates, with the dates spanning 1885 to the 1930's. I'd like to be able to sort by date, but Excel doesn't recognize years before 1900. If I format the cells to show the date as mm/dd/yyyy I can type in the nineteenth-century years and they show that way, but they won't sort. I looked at this thread but didn't see an answer to my question.

While trying to Google this, I found the Wikipedia entry on Y2K says "Unfortunately, the Windows versions of the program by default start at 1900 and the Mac versions by default starts at 1904 (although this can usually be changed)." [italics mine]

Office 2000 with XP.

posted by marxchivist to Computers & Internet (10 answers total)

Treat the column as text, and reformat the contents to YYYY/MM/DD.

While it will be questionable whether Excel could then do date math, it will definitely sort correctly then.
posted by baylink at 11:27 AM on July 19, 2006

Or create three columns -- year, month, and date -- and sort by all 3.
posted by smackfu at 11:56 AM on July 19, 2006

Best answer: There's a workaround here that would probably work for you, as long as you remember to do any sorting using the "Birth Entry" column. There's also an add-in thingy that you can install, but that could cause problems if your spreadsheet will be viewed by anyone other than you (i.e., anyone who doesn't have the add-in installed themselves).

I think when it says "this can usually be changed," it's just referring to the fact that you can switch back and forth between the 1900 system and the 1904 system in Excel, under Tools > Options > Calculation.
posted by Gator at 11:58 AM on July 19, 2006

I don't have anything much to add about this; the previous answers appear to cover it. However, the background to this problem is in this blog post.
posted by matthewr at 12:11 PM on July 19, 2006 [1 favorite]

That piece wasn't all that bad, though Joel, in general, seems excellent at finding fantastic solutions to the wrong problem...
posted by baylink at 12:16 PM on July 19, 2006

You could enter them as Julian dates, which will always sort correctly, and use these macros to convert them to Excel dates.
posted by ldenneau at 12:41 PM on July 19, 2006

Response by poster: Thanks everyone. I think Gator's first link is going to be the best solution for me so far.
posted by marxchivist at 12:50 PM on July 19, 2006

Best answer: How to calculate ages before 1/1/1900 in Excel on Microsoft's Knowledge base.

You might also want to consider OpenOffice Calc that doesn't have this limitation.
posted by Sharcho at 12:57 PM on July 19, 2006

Response by poster: OpenOffice Calc is working great, and does exactly what I want it to (imagine that). It'll probably freak out my IT people, but I might use it for this project. Thanks Sharcho.
posted by marxchivist at 1:47 PM on July 19, 2006

If you get a serious app happening in your org using OOo, and it turns into the thin end of a lovely fat wedge, your IT people will be dancing in the streets and showering you with sweets and flowers, you mark my words.

IT people hate having to keep track of software licences.
posted by flabdablet at 1:03 AM on July 20, 2006