Troubleshooting a dating issue with Excel’s Fill Handle
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).
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.
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.
