Input Mask for Dates

  • Thread starter Thread starter Brenda Rueter
  • Start date Start date
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?
 
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
 
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))<>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

date input mask 1
Input Mask for Dates 2
Reformatting dates for sorting 2
Date input format 2
Input mask for date! 0
How do I create a date format 2
Creating Custom MS-Access Input Masks 0
date year input 2

Back
Top