How can using user defined function in custom data validation?

Question

In my Worksheet I have a Table and want to define Data validation for a column that contained the date, as bellow:

=S2M(B2)<>"Error"

In above, S2M() is a user defined function for converting date from Persian date to Gregorian date, because checking input date is right.

But excel is not letting me use user defined functions in Custom Data validation.

This error shows: A named range you specified cannot be found.

Please note that I was using bellow code in Custom Data validation and that works, right.

=AND(LEN(B2)=10;ISNUMBER(IFERROR(VALUE(MATCH(VALUE(MID(B2;1;4));INDIRECT("intTable[Year]");0)&MATCH(MID(B2;6;2);INDIRECT("intTable[Mounth]");0)&MATCH(MID(B2;9;2);INDIRECT("intTable[Day]");0));FALSE)))

Explain is a Persian date example: 1396/05/25

Thanks.

Answer 1

You can do that. Select B2, or whichever cell in row 2 you want the validation to apply to. Now define a name called, say, IsValid, using:

=S2M(B2)<>"Error"

Now in the data validation box, all you need to enter is:

=IsValid

in the source box and make sure to uncheck the Ignore Blank option.