Input mask in Excell ? 1

[Lothmor]

hi,
I would like to know if one can setup an inputmask in a cell in an Excell spreadsheet. I found nothing about this in the books but before I concluded it couldn't be done, I thought to pose the question here.

Something like : (___-_______-__) where you just have to enter the digits. Or (--:--).

I would appreciate a fast no, it can't be done. But would enjoy very much a yes and a how.
ciao
Raymond

 

[xlbo]

You can use a CUSTOM format of "**:**"
but this will just hide what is there and it won't be converted to time - you would need to use a function on the cell to convert to time (I'm assuming you want a time entry ??)

If you provide a bit more detail on the type of data you want to mask, I may be able to help Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[PepeLeMokko]

Use a custom format on a cell like ###-#######-### ( right click the cell choose format cells > number > custom and type the format uyou want to use in the " Type" box
Typing the values in the cell and after leaving the cell, it will format the way you want
Don't forget to extend the formatting

 

[xlbo]

yeh but that doesn't MASK the input...... Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Lothmor]

thanks Geoff, i will give this a try. My question was in fact a general one about inputmasks but the reason I need it now is for time input. The other difficultie about this, is that I need also to be able to input hours >24 (i.e. 137:45).
Another question would be the conversion from decimal time to normal time (ex. 137,75 = 137:45). I can manage the reverse of this (137:45 = 137,75) but I get odd results trying to do the decimal to hours. Perhaps this should be in another thread...if so I will post it again later.
Thanks for responding Coyote but indeed like Geoff said this is not an inputmask and I still have to enter the dashes.
greetings
Raymond

 

[Lothmor]

hi Geoff, your "**:**" indeed masks (in the sense of concealing the information, but that's not what I meant.
I want to prepare a cell that when I input 1234 shows as 12:34.
And Coyote I tried yours too and found out that formatting with 0's and dashes gives a result like
1234(cell format 00-00) = 12-34. But it seems no to work with semicolons (ex. 00:00)
ciao
Raymond

 

[xlbo]

This is particulary tricky as you may have >24 hours
You can use a custom format of
00":"00
and this will give you the correct LOOK presentationally but the actual value in the cell would be: 1234
To convert this to an actual time value, you would need to use:
=VALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2))
where the entry has been made into A1You will then be able to calc this as a normal time
For time>decimal calcs, remember that in excel, 1 day = 1
therefore 1hour = 1/24, 1 minute = 1 / 1440 and 1 second = 1 / 86400

You will find it much easier to convert times to decimals if you work with the SERIAL number behind the formatted time ie for 12:34, the serial number is 0.52631 ie 0.52631 of a day
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

...I need also to be able to input hours >24 (i.e. 137:45)....

Has anyone actually tried to type

137:45

into a cell?

I would guess not, because if you did you would have seen that Excel takes that as hours:minutes and displays 137:45:00 in the cell, and sets the custom display format for you as

   [h]:mm:ss

If you don't want to see :00 seconds, you can change the custom format to

   [h]:mm

The actual cell content is 5.73958333333333 which represents 5 days plus just under three-quarters of a day. Multiply by 24 and you get 137 and three-quarter hours.

Using Excel 97. I would hope that Excel 2K would not have lost this ability.

 

[xlbo]

Zathras - I know that and xl2000 hasn't lost this ability (AFAIK) - the crux of the question relates to being able to enter 13745 and have it show as 137:45 and then be able to do calcs on it Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

...I get odd results trying to do the decimal to hours...

How are you trying to do this?
To enter 137.75, you can use the formula

   =C6/24

To enter 137.45, you can use the formula

   =((D6-INT(D6))*100/60+INT(D6))/24

 

[Lothmor]

To Geoff and Zathras,
Remarkable how easily you can come up with a solution.
Indeed the 00":"00 works like a charm (I tought I had tried that already), and thanks for the formula to make it able to format as time. I will put it in a hidden colum so it won't disturb the sheet and calc from there.
And thanks to both of you for pointing out the simplicity of the decimal to hours calculation. I was so confused in this that I omitted to check the basics.
I feel helped and I hope this will give you some good feeling too
Perhaps a star will help...
greetings
Raymond