Legal Office Guru header image

Legal Office Guru

≡ Menu

Easier date entry in Excel

woman holding question mark

Unless otherwise noted, all instructions and screenshots are from Microsoft Office for Windows.

A local law office manager contacted me recently with this dilemma:

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

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

WOW – You read that whole post!

Want more Legal Office Guru goodness? Give me your email and I’ll send you
my top tips & tricks for Microsoft Office
PLUS my 22-page illustrated Fast Formatting Fixes report!

Your email and other personal information will never be sold or given to any third party.