0

I have 3 columns, column1 and column2 are Date type, column3 is choise. Now I need create list validation like this:

  1. column2 can be empty or not older than column1

and

  1. when column2 is not empty than column3 cannot be empty and conversely

I created this, but it doesn't work:(

=AND(OR(ISEMPTY([COLUMN2]);([COLUMN2]>=[COLUMN1]));AND([COLUMN2]<>"";(NOT(ISBLANK([COLUMN3])))))

  • Thaks a lot for reply. I found the blog below, but after used it, it didn't work. So I used validation from M.Harrelson (thank), but when I filled out column2 in right date, I couldn't fill out column3. So I start to test it in Excel and here is my validation: =and((if(and(column2<>"",column3<>""),true,and(column2="",column3=""))),(if(column2>=column1,true,isblank(column2)))) – Zbyna Apr 6 '17 at 10:34
0

I found an answer here: http://info.summit7systems.com/blog/part-2-how-to-conditionally-require-data-in-more-than-one-sharepoint-column

I modified the formula on that page as follows:

=IF(AND(ISBLANK(column3)=TRUE,column2<>""),FALSE,
IF(OR(ISBLANK(column2),column2>=column1)=TRUE,TRUE,FALSE))

The first part checks whether column3 is blank and column2 has a value, and the second part checks that column2 is either blank or not earlier than column1.

  • So following @Danny '365CSI' Engelman 's logic, this can be shortened to a single if: =IF(AND(ISBLANK(column3),column2<>""),FALSE,OR(ISBLANK(column2),column2>=column1)) or to just a logical: =AND(OR(AND(column2<>"",column3<>""),column2=""),OR(ISBLANK(column2),column2>=column1)) – M. Harrelson Apr 4 '17 at 17:21
0

Logical Functions return TRUE/FALSE

So

ISBLANK(column3)=TRUE

equals

 ISBLANK(column3)

and

IF(OR(ISBLANK(column2),column2>=column1)=TRUE,TRUE,FALSE)

equals

OR(ISBLANK(column2),column2>=column1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.