Custom number format to allow skipping the key punch of backslash in a date

**dzuspann** · 04-12-2016, 10:07 AM

I want to allow our data entry clerks to avoid entering backslashes when entering dates. I can use custom format 00\/00\/0000 to allow 04052016 to display 04/05/2016. And I understand that it not the serial date in the cell.

Question is can I also avoid entering first 2 digits of year? So if one enters 040516 excel would display 04/05/2016.

**Jonmo1** · 04-12-2016, 10:18 AM

I don't think a custom format will be able to accomodate that.

Honestly, your efforts will be far more effectively spent instructing the data entry clerks to correctly enter dates with the /.
a) You don't have to spend time and effort later converting the entries into real dates after the fact.
b) It will accept 2 digit year entries

Would it be easier for them if they could use period as the date separator ? 04.15.16 ?

Or perhaps have the date entered in 3 different cells, one for Day, One for Month, and One for Year
This way they could press the Tab key (with the left hand) between each part of the date.
Then you can combine them with a =DATE(year,month,day) formula.

**6StringJazzer** · 04-12-2016, 11:00 AM

Another option would be to allow them to type a 6-digit number into a cell that is formatted as Text, then have a formula in another cell to convert that to a date for proper display. The 6-digit cell must be formatted as Text to allow a leading zero.

=DATE(RIGHT(A1,2)+2000,LEFT(A1,2),MID(A1,3,2))

If one enters 040516 Excel would display 04/05/2016 in the second cell (if you use a mm/dd/yyyy custom format).

This could also be done with a macro, thus converting the date in the same cell where it is typed (see attachment). This example will convert dates only when typed into column A.