Input mask for dates (like in Access)?

[Big Pat]

Hi,

Is it possible to set an input mask on cells so that users can enter dates using numbers only, without the slashes?

In Access you can set a field's input mask so that when it is selected the user sees ___ /___ /___ and can key 120707 which will be converted to 12/07/2007.

Is this possible in Excel?

 

[shades]

Yes it is. Select the cell, right-click choose "format cell". On the Number tab, on the left, choose Custom, then on the right in the box (under the word "Type" put this:

mm/dd/yy

Click OK.
________
Herbal vaporizer

 

[unmarkedhelicopter]

Or as a Brit yyyymmdd or dd/mm/yyyy

 

[shades]

Yes, I guess some have backwards other formatting.


... from one who is often backward....
________
Mercedes-benz gl-class history

 

[Big Pat]

Didn't work for me

Hi,

Doesn't that just convert the date serial number to a date format? I tried the cell formatting in both of the previous posts and got these results

mm/dd/yy 06/25/30

yyyymmdd 25/06/2230

dd/mm/yyyy 22300625

In each case, I had keyed the digits 120707 and they all resulted in the same date, but the wrong one, 25th June 2230. When I manually entered that date in another cell and converted the value to a number I got 120707.

My users will not know the number "value" of a date, they just want to be able to key the digits only, but get the right date. They may have up to 400 dates a day to enter so I suppose it's not an unreasonable request as it would save them 800 key-strokes.

I may give up and do this in Access instead but some of them have never even seen Access and I don't want to freak them out.

 

[unmarkedhelicopter]

Do you have a specific range in mind ?
What format would you like for the finished date ?
how would you like users to enter the date 6 digits or 8, ddmmyy or yyyymmdd or something else ?
Would you be happy with code to do this interpretation ?

 

[Big Pat]

Hi,

The range, initially anyway, is from B2 to B10000, though I would need to be able to change this at some point.
The finished format I would like is dd/mm/yyyy
I'd like users to enter 6 digits only ddmmyy (they will all be dates in the next year or so)

As for code, "happy" doesn't cover it exactly but if that's how it has to be, I could probably live with that.

But I've NEVER coded in excel and only a tiny bit in Access, so you'll need to tell me where to put the code, how to save it/run it/whatever.

Thank you so much for your help.

 

[Bogzla]

Hi Big Pat, one thing you could do is
format cells>number>custom
and type 00"/"00"/"00 into the 'Type' box
this will display a typed value of 020507 as 02/05/07, for example. This would be a visual thing only, however, the value in the cell would remain 020507, it would just be displayed to the user as a date.
Alternatively, if the user typed 020507 into cell A3, for example, you could type the following into another cell:

=MID(A3,1,2) & "/" & MID(A3,3,2) & "/20" & MID(A3,5,2)

and that cell would display '02/05/2007'
you could then possibly set a macro to copy this and
paste special>values
back into the original cell...

hth,
Bogzla

 

[unmarkedhelicopter]

Here is a sample file ...
it only does the conversion on sheet2
it only does it in column B
it only does it if you have changed 1 cell
it only does it if you have entered 6 numeric characters (ddmmyy)
it does not do it in row 1

If you hit Alt-F11 whilst on the sheet it will take you to the code the change detection is done under "Worksheet" the actual change code is under "Module1" it is pretty basic and should be self explanatory if you need to change stuff.

 

[lawsonium]

Small point, you example doesn't work if you have your sheet trimming the leading zero!

 

[unmarkedhelicopter]

That's not what he asked for