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

Why cybercriminals are targeting CPAs

This free report expands on the most commonly found scams, why education and specialized IT knowledge help to lessen security vulnerabilities, and why every firm should plan carefully for how it would respond to a breach.

PODCAST

How tax reform — and Excel — are changing the CPA Exam

Mike Decker, the vice president of examinations at the AICPA, discusses changes being made to the exam as a result of tax reform — and about how Excel will now be available for use on the test.