Microsoft Excel: Dating problems

Q. When I paste a large amount of data into Excel containing dates as far back as the 1800s, the recent dates display correctly as valid dates, but the older dates automatically convert to text. I assume I need to adjust a setting in Excel, but I can’t find it. Can you help?

A. Excel handles dates occurring on or after 1/1/1900 perfectly (1/1/1904 in Excel for Mac as a default), but earlier dates are problematic. You can easily see the problem simply by entering 12/31/1899 and 1/1/1900 into Excel using the Long Date format, as pictured below—notice that the earlier date is displayed as text while the later date is displayed as an actual date.

All editions of Excel have this problem in that they were never designed to handle dates earlier than 1/1/1900; Excel’s date system simply numbers the days from 1/1/1900, with “1” representing Jan. 1, 1900, and so on. This anomaly is officially blamed on Lotus 1-2-3, which did not handle leap years correctly, so Excel’s developers included the same errors intentionally to preserve compatibility with Lotus 1-2-3 spreadsheets. To this day, Excel displays Feb. 29, 1900, as a valid date, even though that date did not occur (1900 was not a leap year because it is evenly divisible by 100 but not by 400).

To further compound calendar problems, different parts of the United States converted from the Julian calendar to the Gregorian calendar (which incorporates leap years) at different times using different approaches. As examples, Arizona, California, Florida, Nevada, New Mexico, and Texas followed Spain’s conversion approach, and as a result, the dates Oct. 5, 1582, through Oct. 14, 1582, are not recognized as having occurred in those states. The Mississippi Valley area followed France’s approach, and as a result, that locality does not recognize the dates Dec. 10, 1582, to Dec. 19, 1582. Oregon, Washington, and the Eastern Seaboard followed England’s approach, and as a result, the dates Sept. 3, 1752, through Sept. 13, 1752, are considered to never have occurred. Microsoft Excel does not attempt to account for any of these missing dates; instead Excel simply treats dates before 1900 as raw text rather than true dates, thereby avoiding these complicated date conversion issues.

The most frequently occurring problem caused by this issue relates to sorting date lists composed of dates both before and after Jan. 1, 1900. To sort pre- and post-1/1/1900 dates, you can reformat them using the yyyy/mm/dd custom format; for example, July 4, 1776, would appear as 17760704, and all dates formatted in this manner would sort properly. However, if you need to actually perform calculations using these dates, you have a bigger problem that cannot be solved by changing the format. Two suggestions for overcoming this particular date issue as they pertain to date calculations are as follows:

• Microsoft provides an explanation for calculating ages dating back to before 1/1/1900 using a macro approach at support.microsoft.com.
• The free XDate Excel add-in provides eight new functions to help users work with pre-1900 dates; this add-in is available at j-walk.com.

Also be aware that a secondary problem caused by this date issue is that Excel’s WEEKDAY function returns the wrong day of the week for all dates earlier than March 1, 1900, a problem that can be resolved simply by adding 1 to the WEEKDAY function formula when referring to pre-March 1, 1900, dates, as explained on the Microsoft support page at support.microsoft.com.

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor. Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2013, 2010, and 2007 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.