Troubleshooting a dating issue with Excel's Fill Handle

By J. Carlton Collins, CPA

Q. In the past, I could enter 1/31/2019 in cell B1 and 2/28/2019 in cell C1, highlight both cells, and then drag the Fill Handle to the right to generate the headings 3/31/2019, 4/30/2019, 5/31/2019, and so on. Unfortunately, this functionality no longer works, and I can't find a solution online. Did Microsoft remove this functionality, or am I doing something wrong?

A. I have encountered this type of problem before, and I'm pretty sure your issue stems from the use of a European-style date format rather than a U.S.-style date format as your default Windows setting. Excel's Fill Handle tool does not recognize the European-style date format. This problem can be solved, as follows. In Windows 10, search for and launch the Region & language settings dialog box, select the Date & time option, and then select the Change date and time formats option to display the dialog box pictured below. For both the Short date and Long date options, select a date format that begins with the month rather than the day (such as MM/dd/yyyy rather than dd-MMM-yy).

techqa-6


Exit the dialog box and then close and restart Excel. Thereafter, the functionality you seek should work properly, as suggested in the screenshot below in which the dates in cells B1 and C1 were selected and dragged to the right by the Fill Handle to columns D, E, and F.

techqa-7


About the author

J. Carlton Collins, CPA, (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.

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 QUIZ

How well do you know small business?

There are over 30 million small businesses in the U.S., and many of them are optimistic in their outlook. Are you familiar with the obstacles and opportunities they are facing? Test your small business acumen with this quiz sponsored by Chase Ink®.

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.