Microsoft Excel: Dating problems, part 3

Q. Help! I imported 140,000 rows of data into Excel with each row containing two-digit-year dates formatted as text, but I need to display four-digit-year dates. I’ve tried numerous approaches described on the internet, but none work for all of my dates. Is there actually a way to convert two-digit-year dates into four-digit years?

A. Be aware that Excel has no way of knowing whether the date 03/21/10 refers to 1710, 1810, 1910, or 2010—so the solution you seek may be beyond the scope of Excel’s capabilities, depending upon the date ranges you are working with. That said, presented below are five common formula-based approaches to solving your two-digit-year date problem, all of which fall short to a certain extent. (I’ve explained the shortcomings for each approach below.) You can download the Excel workbook (pictured below) containing all of these examples at carltoncollins.com/2digits.xlsx.

1. REPLACE RIGHT/LEFT/LEN approach. The lengthy formula =REPLACE(RIGHT(A2,2),1,2,LEFT(A2,LEN(A2)-2)&19&RIGHT(A2,2)) deconstructs the text date and then reconstructs that date using a four-digit year by inserting 19 in front of the two-digit year. Unfortunately, this formula approach returns the wrong date for all dates not occurring in the 1900s. This approach also results in a text-based result that is more difficult to sort and work with than date-based results.
2. LEFT/LEN/RIGHT approach. As with solution No. 1 above, the formula =(LEFT(A2,LEN(A2)-2)&”19″&RIGHT(A2,2))+0 also inserts a 19 in front of the two-digit year to solve the problem; therefore this approach also returns the wrong date for all dates not occurring in the 1900s. However, this approach does result in a date-based answer, which is an improvement over solution No. 1.
3. SUBSTITUTE approach. As with the previous two approaches, the formula =SUBSTITUTE(A2,”/”,”/19″,2)+0 also inserts a 19 in front of the two-digit year to solve the problem; hence this approach also returns the wrong date for all dates not occurring in the 1900s. However, this approach does result in a date-based answer and is a simpler solution than approach No. 2.
4. TEXT approach. The function =TEXT(A2,”dd/mm/yyyy”) will display your two-digit years as four-digit years, but this approach adheres to the 1900 versus 2000 assumptions explained in the previous topic. This approach returns a better result than the three previous approaches, but the results are still text-based—hence the results are more difficult to work with.
5. TEXT/DATE approach. The best solution is to use the TEXT approach described in the previous paragraph, coupled with the DATE function, which converts the text-based result to a date-based result, as follows: =DATE(RIGHT(TEXT(A2,”dd/mm/yyyy”), 4),MID(TEXT(A2,”dd/mm/yyyy”),4,2),LEFT(TEXT(A2,”dd/mm/yyyy”),2)). This approach produces a date-based result that follows the assumptions you specify in Excel for handling two-digit years that occur before and after 29. However, you still face the basic problem that Excel can’t tell for sure whether two-digit years are supposed to refer to the 2000s, 1900s, 1800s, or earlier.

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.