Custom data validation fails even though formula evaluation is "FALSE"

So before I ask my question I apologize for the title wording. I was unable to find a generic wording that fits my problem.

I have a table which is given in Table.JPG.

I have custom data validation for my last two columns shown in DV1.JPG DV1.JPG and DV2.JPG.

The Formula in cell L3 is =AND(NOT(ISBLANK(K3)),ISNUMBER(L3),LEFT(CELL("format",L3),1)="D",L3>=K3, YEARFRAC(K3, L3)>=0.5 )

The Formula in M4 is =AND(INT(M4)=M4, NOT(ISBLANK(L4)))

Both formulas fail and as follows, data validation should not allow me to enter that user entry but this isn't the case. The image of the formula evaluation is shown in FormEval.JPG.

So now the motive of the problem; I am trying to make sure that a renew date, Column L can only be entered if a start date exists for a member and this renew date must be at least 6 months from the start date and finally it must be a date greater than the start date given the way the YEARFRAC function works.

Following the above the renew ID, Column M must be set by the user and this renew ID must be an integer and this value can only be entered if a renew date is set.

Based on research I have done so far. The formula looks good so why does it fail in some instances (i.e. when the dependent column is blank)?

1 Answer

For column L in L3 use:

=AND(COUNTA($K3),$L3>=EDATE($K3,6))

For column M in M3 use:

=AND($M3-INT($M3)=0,COUNTA($L3))

When testing empty Date Excel will convert 0 to 01-01-1900 that is why the validation is not working with isblank()
And Edate will add 6 months to the date

CountA will evaluate to True if non empty cell