There is kind of a way to do it by dividing the value you enter using
FLOOR as follows...
For Dates...
Column A - Enter Date e.g. 020878
Column B - Enter the formula =FLOOR(A/[ROW_NO]//10000;1)
Column C - Enter the formula =FLOOR(A/[ROW_NO]//100;1)-B/[ROW_NO]/*100
Column D - Enter the formula
=A/[ROW_NO]/-(B/[ROW_NO]/*10000+C/[ROW_NO]/*100)
Column E - Enter the formula =DATE(D/[ROW_NO]/;C/[ROW_NO]/;B/[ROW_NO]/)
Replace /[ROW_NO] /with the current Row e.g. for A1
A1 - 020878
B1 - =FLOOR (A1/10000;1)
C1 - =FLOOR (A1/100;1)-B1*100
D1 - =A1-(B1*10000+C1*100)
E1 - =DATE (D1;C1;B1)
Hide columns B,C & D in column A you put the date in without the slashes
and it will convert it to date format in column E
For Time...
Similar to above...
Column A - Time e.g. 1037
Column B - =FLOOR ( A/[ROW_NO]//100;1 )
Column C - =A/[ROW_NO]/-B/[ROW_NO]/*100
Column D - =TIME ( B/[ROW_NO]/;C/[ROW_NO]/;0)
With time the time is in column D not E - again replace /[ROW_NO] /with
the current row e.g.
A1 - 1037
B1 - =FLOOR (A1/100;1)
C1 - =A1-B1*100
D1 - =TIME (B1;C1;0)
Regards
Darren.
Glen wrote:
I am doing a spreadsheet and am trying to do what Microsoft calls
"masking" in a cell. I will give 2 examples
First example
I have a date cell and it is using the standard of 02/07/06 .
I want to make it so that when I start the cell I see the slashes and
I don't need to put them in and it goes from the 02 to the 07 without
me having to manually putting the slashes in.
Second example
I have a time cell that uses 09:28. I want to be able to put in 0928
and it separate it out without me having to put the : in manually.
This is probably in the help somewhere and I am just not seeing it
Thanks for your help
Glen
[EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
