Date in Excel

[jenniferbrookplaza]

A coworker has a list of dates she needs to enter in a spreadsheet but she would like to type in the dates without typing the slash key in between the date (example: 040121 instead of 04/01/21) and excel to then add them for her. We formatted the column to a short date but when she stypes 040121 the cell changes to 11/4/2009. Why is it changing the date? Is there a different way to format this?

[#2]

@jenniferbrookplaza 

Excel never recognizes 040121 as the date 04/02/21 by itself. It requires VBA. See Date And Time Entry 

It's up to you to decide whether it's worth the hassle.

[#3]

@jenniferbrookplaza 

Pre-format the cells with Custom Format: 00\/00\/00
When entering the values always use ddmmyy, not ddmyy nor dmyy

(works no problem here - Excel 365)

EDIT: As pointed out by Hans & Sergei the above suggestion doesn't generate the actual date values you expect. As an example entering 010121 will display as 01/01/21, however if you reformat the cell with a Date format you'll see that the actual Date value is 09/16/1297

If you don't know how to convert to enterred values as actual dates, one way below. Assuming the enterred values start in A2, in B2 (or C2...) enter (and copy down as necessary):

=DATE("20"&RIGHT(A2,2), MID(TEXT(A2,"000000"),3,2), LEFT(TEXT(A2,"000000"),2))

(adjust the 20 to 19 if the dates enterred by your coworker are in 19xx)

Then select all cells in the column where you enterred the above formula > Copy > Paste special > Values. Finally delete column A

[#4]

@L z. 

That works in the sense that 110121 will be displayed as 11/01/21. But it will still be the  number 110121, not a date - you cannot use functions such as EDATE, EOMONTH, WEEKDAY etc. with such values, at least not in a meaningful way.

And it won't prevent the user from entering invalid "dates" such as 65/43/21

[#5]

@L z. 

The only that will be some numbers which looks like dates, not dates

[#6]

@Hans Vogelaar & @Sergei Baklan 

I assumed @jenniferbrookplaza  would know how to convert a non-date (resulting of what I suggested) as a date value. I'm gonna edit my post with one option. Thanks for pointing this out though

Re. entries like 65/43/21 there's nothing we can do, even on a non-preformatted cell

(was just trying to help with a possible way to enter the data as she wanted)