Sorting Dates in Excel for Your Genealogy Timelines

Timelines are awesome tools for genealogy. They can help us spot inconsistencies and identify gaps in our research. Spreadsheets, such as Excel, make it easy to list the events. But there’s a problem with Excel. When it comes to sorting dates before 1900, Excel doesn’t know what to do with them.

Here’s an example of a work-in-progress timeline. Yours might look like this as you’re beginning to compile it.

A work-in-progress timeline in Excel. Note the dates are not in order.
A work-in-progress timeline in Excel. Note the dates are not in order.

I’ve entered the events as I came across them, so they are not in chronological order. He needs to enter the army in 1862 before he’s discharged in 1864. Also, his wife’s death in 1903 occurred before he died in 1910.

So what happens when we sort on column A?

The timeline after sorting on column A.
The timeline after sorting on column A.

Here’s where Excel is exceedingly stupid. It put the post-1900 dates in order, but because it doesn’t recognize anything before 1900 as a date, it treats those entries like a string of characters. That’s why we have a date in October (10 – that’s a one and a zero) coming before April (4) and May (5). And the years? Those are pretty much ignored since Excel didn’t need to go that far down in the string to sort them.

Sigh.

Here’s How to Trick Excel Into Sorting Dates Correctly

Since Excel apparently thinks that time began in 1900, we need to force it into looking at the entire string and to put those strings in what we know is chronological order. There are a couple of ways to get Excel to sort the dates correctly.

Option 1: Format Your “Date” Column as Text

Excel has this nasty habit of assuming it knows what you want a cell to be. For example, if you type in 1-31-05, it will assume you mean January 31, 2005 and reformat that cell as a date. Even if you type in 1-31-1905, it will still assume it’s a date. The problem is that it doesn’t assume that 1-31-1899 is a date.

Before you start entering, format that column as Text. Here, I highlighted the column where I’ll be entering the dates. Then I clicked the dropdown on the Number formatting and selected “Text.”

timeline-3I’m on a Mac. If you’re using the Windows version of Excel, this might look a little different. However, the same functionality is there for you, too.

Now when I enter a date, I enter it as YYYY-MM-DD. Here’s that same work-in-progress timeline with the date column formatted as Text and the dates entered as YYYY-MM-DD.

timeline-4Now let’s see what happens when I sort on that column:

timeline-5We have a winner!

When you use this method, it is important that you make the month and the day 2 digits, otherwise, you’ll have October (one zero) coming before February (two).

Option 2: Record the Day, Month, and Year in Separate Columns

You can also set up 3 separate columns for the date: one for the year, one for the month, and one for the day. No formatting of the columns is required.

timeline-6Then when you sort, tell Excel to sort first on the year column, then on the month, then on the day. Here’s how that option looks on my Mac. I clicked the + button to add the additional columns I wanted to add to the sort order. (Windows might look a bit different, but it has the same functionality.)

timeline-7Here’s how it sorted:

timeline-8Use Whichever Method Works Best for You

One of the things that I love about Excel is how flexible it can be… except when it isn’t! Even though it isn’t smart enough to figure out that there really are dates before 1900, we can still bend Excel to our will and make it sort the way we want it to. Whether you use the format-as-text method or the separating-year-month-and-day method, you can get Excel to create a timeline that’s in the right order.

(Thank you to Michele Simmons Lewis, who inspired this post.)

Posted: July 21, 2015.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • Hi Amy
    Thanks for the tip on using Timelines. It helped me to find missing data that I needed to fill in.
    Thanks
    Linda

  • I use a variation of your option no. 1 — yyyymmdd all as a one “word” entry: 20010101, for example. it takes a little getting used to, but saves space. Using this approach, no matter the format I’m using word, excel, etc. it keeps things nice and neat. For example, when I save snippets of newspaper articles, I name them by date, source, and then the family name I’m researching: 18740517 phil inq jones (using a shorthand for the newspaper). This way, after I’ve transcribed the items — entering by the same date format into a word doc, I can easily go back to my “original” in case I’ve made an error somewhere. I didn’t start off transcribing all the articles, but found I was missing what turned out to be critical clues later on. I’ve created a document for each family name containing transcriptions of articles. As you’ve mentioned elsewhere, newspapers are wonderful. Wish more were available on-line.

    thank you so much for your blog.

  • We use this format for everything including leading text in filenames as it’s sortable and used by IT, engineering, worldwide. But just downloaded Rootsmagic and it’s not an available date format preference. Guess we will ask in their forum