Force cell entry strictly to mm/dd/yyyy

[odonnell]

Using Excel 2007, and I want to force entry to calls in a date column to the format mm/dd/yyyy. Currently I have the cells formatted for mm/dd/yyyy and entered data validation date range between 12/31/1999 and 12/31/2060. However some users are too lazy to look up the day case start and just enter 05/03 (mm/yy) and data validation allows this, then excel formats the date as 05/03/2015.</SPAN>
I don’t want to use a macro as macros will be disabled by default and they wont enable it.
I tried to make data validation = text length 10 however it has issues with the ‘/’</SPAN>
Is it possible to make the data validation mm/dd/yyyy and strictly not accept anything else?</SPAN>
thanks guys</SPAN>

 

[oldbrewer]

in an adjacent cell

=if(len(A1)<10,"oops you seem to have entered the date to the wrong format","")

 

[Jonmo1]

Without VBA, it's not going to be easy.

Basically what happens is the 05/03 is converted to the date 05/01/2015 before data validation even looks at it.
This is why the Len=10 rule doesn't work.
Because the real value of a date is a serial number, incrimenting by 1 since Jan 1 1900.
So 3/11/2015 = 42074
LEN(42074) = 5

The only solution I can imagine (without VBA) is to format the entry cells as TEXT.
Then you can use the LEN = 10 rule.

Side effect is that any formulas that refer to them will no longer consider them dates, they'll be text strings..
You'd have to have a helper column to do =A1+0 to convert them to dates for your other formulas to refer to.


But even this is still not fool-proof.
It will not tell the difference between a date entered as mm/dd/yyyy and a date entered as dd/mm/yyyy

 

[gaz_chops]

Or create 3 cells with data validation "lists" 1 for days, 1 for months, 1 for years, then use =date(c1,b1,a1)

 

[oldbrewer]

07	26	15	7/26/15										15	01	01
													16	02	02
													17	03	03
													18	04	04
													19	05	05
													20	06	06
														07	07
														08	08
														09	09
														10	10
														11	11
														12	12
														13
														14
														15
														16
														17
														18
														19
														20
														21
														22
														23
														24
														25
														26
														27
														28
														29
														30
														31

<colgroup><col><col span="16"></colgroup><tbody>
</tbody>

 

[gaz_chops]

Maybe long winded but set the lists by month!
Someone is bound to enter 31/2/2015
Which would return 3/3/2015 (I think)

 

[odonnell]

Thanks guys, some good ways I never thought about. Will test all out and see what works.
Cant beleive i didnt think about the list option! doh!

 

[gaz_chops]

You're welcome.