question

DanielBrokhman-7908 avatar image
0 Votes"
DanielBrokhman-7908 asked YiLu-MSFT edited

Sharepoint Validation Not Working

No matter what I do the validation for these 3 formulas don't work

  1. if planned end date is >= than today and completion status is smaller than 100% ask for validation

  2. if planned end date is >= than today and the status is <> than completed then ask for validation

  3. if the completion status is 100% and the status <> than completed ask for validation

MY INPUT:
1.=IF(AND([Planned End Date]>TODAY(),Completion=100),FALSE)
2.=IF([Calculated End Date]>=TODAY(),Status="Completed")
3.=IF(Completion=100,Status="Completed",TRUE)

Notes: Status is a column that allows for a dropdown. One option from the dropdown is "Completed"
:Completion is a column that allows for a number input between 1-100.

office-sharepoint-onlineoffice-sharepoint-server-administrationsharepoint-devoffice-sharepoint-server-development
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

YiLu-MSFT avatar image
1 Vote"
YiLu-MSFT answered YiLu-MSFT edited

Hi @DanielBrokhman-7908
You could use the following formula:

    =OR([Planned End Date]<TODAY(),Completion=100)

1.As a result, if planned end date is >= than today and completion status is smaller than 100%, the validation works:

202151-image.png


 =OR([Planned End Date]<TODAY(),Status="Completed")

2.As a result, if planned end date is >= than today and the status is not equal to "Completed", the validation works:

202124-image.png


 =OR(Completion<>100,Status="Completed")

3.As a result, if the completion status is 100% and the status is not equal to "Completed", the validation works:

202161-image.png


As you explained that Status is a column that allows for a dropdown. One option from the dropdown is "Completed", I create Status column using a choice column type.

What's more, if you want to use the three formula at the same time, you could use:

 =AND(OR([Planned End Date]<TODAY(),Completion=100),OR([Planned End Date]<TODAY(),Status="Completed"),OR(Completion<>100,Status="Completed"))


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





image.png (23.6 KiB)
image.png (27.3 KiB)
image.png (26.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.