Input Mask for Dates

B

Brenda Rueter

I have a number of users (Excel 2000 and 2002) who want the ability to put
dates into cells without typing the slashes. This would best be described
as the "input mask" available in Access. Is there anything available in
Excel to do this?
 
G

Gary Brown

You can't do exactly what you can do within Access, unless you want t
run some sort of macro, however, you can use data validation to ente
dates without slashes into one cell and get excel to convert the tex
entry in the adjacent cell into a correct date.

The data validation could be a custom data validation, assuming th
data entry cell is A1 with a text formating and the resulting dat
appears in B1, then

=AND(IF(VALUE(LEFT($A$1,2))<>DAY($B$1),FALSE,TRUE),IF(VALUE(MID($A$1,3,2))<>MONTH($B$1),FALSE,TRUE),IF(VALUE(20&RIGHT($A$1,2))<>YEAR($B$1),FALSE,TRUE),LEN(A1)=6)

The date would need to be entered as DDMMYY, with a day entered as 0
not just 6
 
B

Brenda Rueter

Thanks for the idea.

Gary Brown said:
You can't do exactly what you can do within Access, unless you want to
run some sort of macro, however, you can use data validation to enter
dates without slashes into one cell and get excel to convert the text
entry in the adjacent cell into a correct date.

The data validation could be a custom data validation, assuming the
data entry cell is A1 with a text formating and the resulting date
appears in B1, then
Click to expand...
=AND(IF(VALUE(LEFT($A$1,2))<>DAY($B$1),FALSE,TRUE),IF(VALUE(MID($A$1,3,2))<>
 
Top