Custom Data Validation Formula not working as expected

[ashleighbrown]

Hi,

I'm trying to set up some data validation so that the cells only allow numbers input written in the format "XX/XX" where X is any numeric value, and the / separates the two. Basically it is going to be a way of showing age in years and months.

I have the following custom formula in data validation:

'=AND(ISNUMBER(LEFT(AE2,2)+0),MID(AE11,3,1)="/",ISNUMBER(RIGHT(AE2,2)+0))'

For some reason, the validation is allowing some numbers (e.g. it allows 45/07 and any starting number above 31, but won't allow 12/07) since the spreadsheet is for children of school age, the numbers I need people to be able to input are between 10-14 so not really sure why the formula likes higher numbers as opposed to the lower ones. Could anyone shed some light?

Thanks!

 

[ashleighbrown]

nevermind I've worked it out! It wasn't the formula but the format of the cells trying to change to a date format - i've changed to a Text format and no issue now!