A local law office manager contacted me recently with this dilemma:
Reader Question
If I format the column as a date column so that my dates look like 05/12/16, all is well as long as I put in the slashes. I’ve got tons of dates to input and if I could simply put in 051216 and let IT put in the slashes, that would be wonderful – but when I do enter 051216, Excel changes it to 03/21/40. What’s it doing and how can I fix this?
Normally, speedy data entry isn’t a problem in Excel. As long as you set up the “where the cursor goes after you hit Enter” setting correctly, you can just type away.
Dates, however, are a bit of a pain in the … neck. As our hapless office manager has noted.
Unfortunately, what she was bumping into is this: behind the scenes, Excel stores a date as a sequential number, so if you type in a number, it’ll just try to translate it into a date.
If you’ve got a ton of dates to input, there’s a workaround. You’ll need two columns:
- a column to type your date in without the slashes or dashes, and
- another column to “translate” that into a date format.
The “data entry” column would need to be formatted as text so you wouldn’t lose your leading 0, etc. when you type in “05122016”. (Yes, you’d need to type in a 4-digit year.)
The second column, formatted as dates, would need to contain a formula like this:
=DATE((MID($A1,5,4)),(MID($A1,1,2)),(MID($A1,3,2)))
… assuming column “A” is your data entry column and your data entry row starts with “1”.
That will extract out the four digits of the year, the two digits of the month, and the two digits of the day from your data entry column and transform them into a date.
You could then hide the data entry column in your final report (but you can’t delete it entirely unless you copy the values from your Date column into another column).
