PDA

View Full Version : Solved: input masks



cmpgeek
07-01-2004, 06:55 AM
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
07-01-2004, 10:33 AM
You have to do it through code.

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

jamescol
07-04-2004, 11:43 PM
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
07-08-2004, 08:57 AM
Hey, where you at on this one? These guys help you figure it out?

cmpgeek
07-08-2004, 12:48 PM
: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
07-08-2004, 12:50 PM
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
07-08-2004, 03:33 PM
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
07-08-2004, 04:01 PM
Another way to get there would be to...


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

jamescol
07-08-2004, 04:19 PM
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
07-09-2004, 10:53 AM
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
07-09-2004, 11:00 AM
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
07-09-2004, 11:54 AM
:mkay 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
07-09-2004, 12:19 PM
You can work around it. Sample file attached.

cmpgeek
07-09-2004, 12:25 PM
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!