Input masks

[cmpgeek]

is it possible to create input masks to use in excel? i know there are some special formatting options available for things such as SS #, phone numers, & zip code, but there doesnt seem to be a way to add anymore - i just wondered if there was a coding way to add more? the ones i am mainly interested in seeing are a date mask dd/mm/yyyy and a time frame mask [h]:mm

i know i can format the cells to look this way, but i am wanting an actual input mask so that all the user has to do is enter the numbers and Excel adds the /s and :s where ever needed...

any ideas yall have would be wonderful...
thanks

[Juan Pablo Gonz?lez]

You have to do it through code.

http://www.cpearson.com/excel/DateTimeEntry.htm

[jamescol]

You can also define Custom formatting via the GUI:

1. From the menubar choose Format
2. Then choose Cells...
3. Click on the Number tab of the window that appears
4. Scroll down to the bottom of the Category list and highlight Custom
5. Define your custom format in Type: field that appears on the right

For instance enter (###,###.0000001) in the Type field if you want to format the number 123456 as

(123,456.0000001)

To learn the various formatting codes available, use Excel Help and search on the following index words: number;format;code. The Format Number Codes should be in the search results pane.

James

[Zack Barresse]

Hey, where you at on this one? These guys help you figure it out?

[cmpgeek]

:no i have been in classes and meetings all week as well as covering for 2 co-workers who were both off for a week so i havent even had a chance to try it yet... (i am only here now because this last meeting let out an hour early...) hopefully i will get to be in my office all day tomorrow and will try it first thing!

wish me luck...

[cmpgeek]

You can also define Custom formatting via the GUI:
James

Whats GUI? i have heard reference to it before, but have not had the chance to research it yet...

thanks

[jamescol]

Sorry - GUI stands for Gaphical User Interface. It just means the standard interface (screen) you get when you start the program vs. using code or a command line like in DOS.

So for my instructions, just start Excel and then start at step #1.

James

[Zack Barresse]

Another way to get there would be to...

1. Press Ctrl + A + 1
2. Click Number (tab)
3. Scroll/select Custom
4. Enter format (###,###.0000001)

[jamescol]

I don't have enough brain cells left to remember the shortcut keys and the menu options! Getting older isn't as fun as it used to be Uhh - what was this thread about?

[cmpgeek]

You can also define Custom formatting via the GUI:
1. From the menubar choose Format
2. Then choose Cells...
3. Click on the Number tab of the window that appears
4. Scroll down to the bottom of the Category list and highlight Custom
5. Define your custom format in Type: field that appears on the right
James

this will not do what i am looking for.
Ex... type 01012004 into a cell and then go to the number tab undre formatting as you described it above... if you highlight Custom and then choose the "mm/dd/yyy" option it turns 01012004 into 10/08/4670...

what i want to be able to do is type in 01012004 and when i hit return have Excel automatically switch that to 01/01/2004.


and for times, i want to be able to enter 1730 and have it convert it to 17:30
(meaning seventeen hours and thirty minutes, not military time)

im going to try the coding that was suggested now...

[Zack Barresse]

You can't do that. You can either have it appear as text, like you want it; or you can input the date (serial number) that excel will recognize as an actual date. The problem is Excel here, it won't recognize your format as a *real* date. You'd need to have it as text, then reformat any dependents relying on it.

[cmpgeek]

I was afraid that was the answer... oh well - i need the other functions to see it as a date more than i need to worry about the extra keystrokes...


i appreciate all the help yall have given me! and special thanks for the cpearson website - i found several answers there that i had been wondering about...

thanks again
xoxox

[Zack Barresse]

You can work around it. Sample file attached.

[cmpgeek]

thanks... im not sure how you worked it, but i will figure that out in a little bit... i need to finish up some last minute details for an inservice for Monday before i head home...

thanks again yall - this site is awesome!