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

In focus: Payroll

Providing payroll services that comply with ever-changing regulations and meet evolving employee and employer demands is no easy task. Paychex's Tom Hammond discusses common payroll considerations for CPA firms.

PODCAST

A closer look at threats to CPA licensure

Legislation in states around the country could put CPA licensure in danger. This episode examines what practitioners need to know to help ensure the profession is protected.