how can I restrict data input for certain columns???

[fry]

Hi All

I've had a look at data validation but can't see how to solve my problem therefore any help gratefully received...

I'd like to restrict the value a user puts in to the format xx/yy or x/y which in this case is a date.

If anything else is entered a warning message is displayed with maybe the correct format to enter....

Is this possible????

 

[wisewood]

Is the date you are inputting a dd/mm or mm/yy format?

You can use a combination of data validation > date > greater than 01/01/1900...

and then format the cells to display custom > dd/mm or mm/yy depending what you need.

 

[fry]

It's dd/mm or d/m.

I think you can format a custom mask to the above but I'm just not sure how to do it....

I need people to use the "/" key though rather than the "-" or the "." so that's why I wanted to be precise...

Thanks

 

[ADAMC]

Fry.

Highlight the whole column where the data entry will take place:
choose data from the menu and choose 'validation.

In the allow field choose date
From the data drop down choose 'greater than'
in the start date type 01/01/1900
If required go to the error alter tab and write a message something like must be entered in the format of DD/MM

Click okay

with your column highlighted, right click
choose format cells
select 'custom'

type DD/MM now click okay.

try typing something in the field which isnt in your format....

 

[fry]

Hi Adam

I tried the above but it still allows me to enter 7.8 and returns a value of 7th January 1900????

 

[Peter_SSs]

fry

Are the cells formatted as Text, Date, General, other?

 

[fry]

Hi Peter

The cells are formatted as Adam suggested, custom dd/mm

Thanks

 

[Peter_SSs]

Hi Peter

The cells are formatted as Adam suggested, custom dd/mm

Thanks

If the cells are formatted with a custom date, then the underlying result in the cell is just a number. Whether you type 25/10 or 25-10, the number in the cell is 39380, so I'm struggling to see why it matters which one the user types? What don't I understand about what you are trying to do?