Microsoft Excel: Dating problems, part 2

By J. Carlton Collins, CPA

Q. Help! My future value calculations don't make any sense (see the spreadsheet below). The formula in column D is the same for all rows, but for unknown reasons, it stops working after row 7. What's going on?

techqa-5

 

A. Your formula is perfect, but your worksheet reveals a date problem all CPAs should be wary of relating to entering two-digit-year dates. When you enter a two-digit year using the digits 00 to 29, Excel then assumes you mean 2000 to 2029; but when you enter dates into Excel using the two digits ranging from 30 to 99, Excel assumes you mean 1930 to 1999. If you expand the formatting in column C to display four-digit years, you can instantly see that the dates on rows eight through 10 refer to 1900 dates, not 2000 dates, as pictured below. You only need to edit your dates to solve your problem.

techqa-6

(Note: By default, Excel displays four-digit years for all dates, so ordinarily you should have noticed this problem right away. However, in this case it appears that you have changed your default year format to display two-digit years, just as I always do with Excel so that my dates fit in the default Excel column width.)

You can avoid this type of problem simply by entering four-digit years instead of two-digit years, so Excel does not have to guess which century you mean. Another option is to adjust Excel's two-digit-year interpretation settings, as follows. In Windows 8's Control Panel, select Region (or Clock, Language, and Region and then Region and Language in Windows 7 or Vista), click the Additional Settings button, select the Date tab, and then in the Calendar area adjust the interpretation settings for two-digit years, as pictured below. You will then need to restart Excel.

techqa-7

This adjustment will change the default way two-digit years are handled for all of your programs that abide by your Region settings (i.e., not just Excel but programs such as Access and Outlook as well).


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.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.