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])))))

Improve this question
1
  • 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)))) Commented Apr 6, 2017 at 10:34

2 Answers 2

Reset to default
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.

Improve this answer
1
  • 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)) Commented Apr 4, 2017 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)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.