Force data entry in nn/nn/nn format when not a date

[mrcann]

I have some text fields in which I want to enter numbers in the format nn/nn/nn (e.g. 65/45/12). Some of these numbers look a lot like dates.

I want to validate the data entry into these cells in the format nn/nn/nn without them being treated as dates.

Can anyone help?

 

[Scott Huish]

Format the cells as Text first or type a ' first

 

[mrcann]

Thanks Hotpepper

I have it formatted as text - the challenge is how to force someone to put a / in positions three and six.

 

[Stephen_Tsui]

Mrcann:

See if below what you want.
Select Cells A1
Data > Validation > Custom > Formula=AND(MID(A1,3,1)="/",MID(A1,6,1)="/")

 

[mrcann]

Thanks Stephen,

Data validation on my computer will not work with that formula, but it works a treat in the spreadsheet.

I have inserted a new column as a validation column, and have extended the formula a little as follows:

=IF(AND(MID(K2,3,1)="/",MID(K2,6,1)="/", VALUE(RIGHT(K2, 2))<100, VALUE(LEFT(K2, 2))<100, VALUE(MID(K2, 4, 2))<100), "Correct", "Incorrect")

Thanks!