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.
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?
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.
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.”
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.
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.
Then 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.)
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.)