Microsoft Excel reader tip: Simple and elegant date conversions

By J. Carlton Collins, CPA

In response to the August Technology Q&A topic "Dating Problems, Part 3" (page 85), reader Daniel Warco, CPA, (controller for the Elizabeth Glaser Pediatric AIDS Foundation in Washington) sent me a better solution than the ones I had ­suggested. As a reminder, the Excel question involved converting two-digit-year, text-based numbers into four-digit-year, date-based numbers. I presented five possible solutions, all of which were fairly complicated. Daniel said that you can simply add 1 and then subtract 1 from the two-digit-year, text-based number and then apply four-digit-year date formatting to convert the number to a four-digit-year, date-based number, as pictured below.

techqa-2


This tip works because the process of adding 1 and then subtracting 1 converts the
text-based date to a numeric-based date, and previously I did not know that. (Thanks, Daniel. I plan to teach this tip in my next Excel course, and I'll act as if I've known about it my entire life.)


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

6 key areas of change for accountants and auditors

New accounting standards on revenue recognition, leases, and credit losses present implementation challenges. This independently-written report identifies the hurdles that accounting professionals face and provides tips for overcoming the challenges.

PODCAST

How tax reform will impact individual taxpayers

Amy Wang, a CPA who is a senior technical manager for tax advocacy at the AICPA, answers to some of the most common questions on how the new tax reform law will impact individual taxpayers.