When working with a date range that spans multiple centuries (for instance, late 1800s to present), it’s important to know a few things before viewing or saving the data in Excel. (I’m currently working with Excel 365 for Mac and Excel 2016 for Windows.)

Suppose you’re working with data stored in a CSV file and want to examine it in Excel. Here is a short list of things to watch out for:

1. Excel for Mac automatically formats dates in m/d/yy format, shortening years to two digits in the process. (Thus 1915-02-08 becomes 2/8/15!) If you then save back to CSV, it will overwrite four-digit years to two, thereby ruining your date fields — as there will be no record of which century it’s from. You’ll need to go back and recover four-digit years from your source. This is bad.
2. Excel for Windows defaults to m/d/yyyy format. This is not so bad, as the full four-digit year values are maintained.
3. Neither Excel for Mac or Windows recognizes dates before 1900, instead treating such dates as text. (Thus 1898-01-01 remains ‘1898-01-01’, as text.) On the plus side, it does not change the formatting of these dates.
4. For the above reasons, if you view date fields in Excel for Mac or Windows, it makes good sense to immediately format your dates to yyyy-mm-dd (following the international standard for data formats: ISO 8601). This requires using custom formatting in Excel. But it’s effective and can save your bacon. (Plus, it jibes with Python pandas and R.)

To reformat dates in ISO 8601 format in Excel for Windows:

• Go to Format Cells and select the Number tab.
• Then use the Custom category, and type in the formula: yyyy-mm-dd

In Excel for Mac, the process is similar, but the option we need is (currently) available under the Date category:

• Go to Format Cells and select the Number tab.
• Then use the Date category, and select the option starting with a four-digit year, followed by a two-digit month and two-digit day, with hyphen separators. (Excel for Mac currently displays this with the sample date: 2012-03-14.)
• Alternatively, do as in Windows Excel, and enter it as your own Custom format: yyyy-mm-dd.

For Further Reading

• Format a date the way you want in Excel for Mac, Windows, or Online – support.office.com
• The Year 1900 Problem – Wikipedia
• How to Work with Dates Before 1900 in Excel – Excel User
• How to calculate ages before 1/1/1900 in Excel – Microsoft