Microsoft Excel: Dating problems, part 3

By J. Carlton Collins, CPA

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.

techqa-8

 

  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.

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.