Microsoft Excel: Dating problems, part 2

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?

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.

(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.

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.