I stumbled on this one quite by accident. I like to have my dates completely written out like this: 14 February 1862. Excel does not allow you to format dates this way, well, it won’t let you if you happen to be in the United States. If you change your location to English (United Kingdom) you can format your dates this way.
Another thing I stumbled on. If you have been using Excel for any length of time you will know that it doesn’t like pre 1901 dates. You have to format the cells containing dates as Text instead of dates. I noticed that when I imported a CSV file that I created in Legacy that my pre 1901 dates were intact, they were being treated like text because Excel didn’t recognize them as dates. A further clue that Excel didn’t recognize them as dates is that they were justified left. So far so good. However, the post 1900 dates were imported with two digit years (because of the US setting) and they were justified right (made them easy to spot). I was able to fix all the dates so that they were uniform and correct. I want to thank my coworker Sherry from Legacy Family Tree for helping me test this and figure out the solution.
1) Import the data (CSV file)
2) Change to the English (United Kingdom) date formats and choose DDMMYYYY format*
3) Justify the column to the left (or right) to line all the dates up
* You will need to change to whatever format your imported dates are in. You will not be able to globally change these to another format because all of the pre 1901 dates are not being seen as dates. I chose the format that was imported from a Legacy CSV file which is full dates. If I had my dates entered in Legacy as 15 Jan 1900 then I would have to use that format.
NOTE: You will not be able to sort by the date column. Excel will only see the post 1900 dates as dates so anything before 1901 will not sort. If you need to sort by date you will have to split the year from the rest of the date and then sort by the year column, then by the rest of the date column. These columns will be treated like simple numbers so they will sort properly.
Copyright © 2015 Michele Simmons Lewis