Easier date entry in Excel

0 Comments

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:

  1. a column to type your date in without the slashes or dashes, and
  2. 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”.

Excel-2016-date-conversion-formula

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

About the author 

Deborah Savadra

I spend an inordinate amount of my time playing with computers and attempting to explain technology to lawyers and law office staff. It's not always easy, but someone's got to do it.

But, seriously, I'm a law firm software trainer by trade with nearly 30 years of experience in and around law firms and their technology. This blog is my attempt to spread the word about better and more efficient ways to use Microsoft Office in a legal practice context.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

How do your Word skills stack up?

Whether you're getting ready for a job interview or just looking to "skill up" to meet daily demands, this Word Skills Checklist can help you find the gaps in your knowledge of basic to intermediate Microsoft Word skills. Click the button below to download your copy today!