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 WHITE PAPER

Preparing the statement of cash flows

This instructive white paper outlines common pitfalls in the preparation of the statement of cash flows, resources to minimize these risks, and four critical skills your staff will need as you approach necessary changes to the process.

RESOURCES

Keeping you informed and prepared amid the COVID-19 crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.