Column Validation help

Question

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

Danny '365CSI' Engelman · Accepted Answer · 2015-10-27 12:40:20Z

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

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
– 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
– 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] — Danny '365CSI' Engelman
– Danny '365CSI' Engelman, Commented Oct 27, 2015 at 13:13

1 Answer 1