Forum Discussion
Date in Excel
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
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
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)