If column [Status] = 'Approved' then [Run Date] must be >= today()

I cannot seem to get the syntax for this validation correct. Can anyone help out?

I understand the validation has to return true.

  • Hi Gen, are you trying to do list validation? If so I don't believe this is the right type of syntax at all. I can help you out though, what're you trying to accomplish? A bit more context would be useful. Thank you! – KGlasier Dec 12 '18 at 21:55

I think you should take a look at the validation functions for SharePoint. It's very similar to Excel.

A reference for Validation expressions

A couple things to keep in mind:

  • Validation equations must return True or False
  • Column validations can't refer to other columns (in this case you'll need to do a List validation, not column validation)

So we'll need some sort of boolean statements returned... I'll assume your statement is to return True if [Status] not equal to "Approved" Try something like the following:

if( [Status]="Approved", [Run Date]>=Today(), True )
  • Hi KGlasier! Thanks, this works wonderfully. Another question, is it possible to have list validation on multiple columns with different messages when each validation fails? – Gen Dec 13 '18 at 16:17
  • As far as I'm aware, no. The best you can do is have a larger single validation that covers all your requirements then have a message appear stating something like "Oops! Please ensure all of the following are correct: {...}". If you need help with something like that, please let me know! Also if you can mark my answer correct (the checkmark to the left), if it solved the issue, it'll let others know what option you chose. – KGlasier Dec 13 '18 at 16:23
  • I would love some help on combining several statements. For example, combining the above statement with something like [date1] >= [date2]. So date 1 must be greater than or equal to date 2, there is no if condition. Thanks! – Gen Dec 13 '18 at 20:10
  • @Gen To combine statements would be to use the AND function. You can have something like the following to have the Date comparisons and the Status checks together: =AND( [date1]>=[date2], if( [Status]="Approved", [Run Date]>=Today(), True )) This will check first if the dates are valid, then if they are valid they'll check the [Status] statement from before. If both return true then the check was valid. – KGlasier Dec 13 '18 at 20:33

Assuming that you are looking for list validation formula, the formula would look like below. Here is a support article from Microsoft on formulas


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.