0

I am struggling to get my column validation to be accepted.

This is the logic I am trying to replicate:

IF "Status" = "Successful" 
THEN "Date Completed" must be populated 
AND "Date Completed" IS NOT in the future.

Here is the formula I currently have:

=IF(Status="Successful",
IF([Date Completed]<>"",TRUE,FALSE),TRUE)
AND
=[Date Completed]<=Today()

1 Answer 1

0

A filled in Date field is a TRUE value (and an empty Date = FALSE)

The SharePoint AND is a Function, not an Operator (like in normal programming languages)

=IF(Status="Successful"
    ,AND( 
          [Date Completed]
         ,[Date Completed]<=Today()
        )
    ,TRUE
   )

Extra condition: Creator of the item may not edit

=IF( [Created By]=[Me]
     ,FALSE
     ,IF(Status="Successful"
        ,AND( 
             [Date Completed]
            ,[Date Completed]<=Today()
            )
        ,TRUE
       )
   )

I think this works, haven't tested it

Note: You can not get a custom feedback messsage for every condition

3
  • Excellent! That works exactly as expected, thank you so much! Would it be hard to implement another condition to the rule stating that this change can NOT be made by the user the record was "created by"?
    – Jack
    Commented Oct 27, 2015 at 12:19
  • The edited formulae with the extra condition causes this error on save: "One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas." Is "Created By" a column that can be used in validation formula?
    – Jack
    Commented Oct 27, 2015 at 12:45
  • No, it is most likely the [Me] causing the issue. Means you can not check for the current user. Only in View Settings can you use [Me] Commented Oct 27, 2015 at 13:13

Your Answer

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

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