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

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.

COLUMN

Deflecting clients’ requests for defense and indemnity

Client requests for defense and indemnity by the CPA firm are on the rise. Requests for such clauses are unnecessary and unfair, and, in some cases, are unenforceable.