1

I have a list in SharePoint with a date column "Complete Date". I want to add a control to only allow users to select the complete date in current quarter. I used this formula:

=IF(TODAY()>=DATE(YEAR(TODAY()),10,1),OR(ISBLANK([Complete Date]),[Complete Date]>=DATE(YEAR(TODAY()),9,30)),IF(TODAY()>=DATE(YEAR(TODAY()),7,1),OR(ISBLANK([Complete Date]),[Complete Date]>=DATE(YEAR(TODAY()),6,30)),IF(TODAY()>=DATE(YEAR(TODAY()),4,1),OR(ISBLANK([Complete Date]),[Complete Date]>=DATE(YEAR(TODAY()),3,31)),OR(ISBLANK([Complete Date]),[Complete Date]>=DATE(YEAR(TODAY()),1,1))))))

However, this causes error when I edit any other columns for the existing records with the complete date in previous quarter too, even though the complete date is not being touched. Is there any way I can only apply the validation on the amendment of the Complete Date?

Improve this question

1 Answer 1

Reset to default
0

Column level validations in SharePoint online lists are applied at column level for all the existing list items/records as well as the for the new records users will be adding to the SharePoint list.

Column level validations will be invoked when you are editing existing list items as well even when you are not changing that particular date field in list forms. Hence SharePoint will force you to select the date in current quarter for "Complete Date" column.

This is a default behavior in SharePoint and unfortunately you cannot change this behavior while using column level or list level validations in SharePoint.

If you want to update all list items one time, you can complete the updates to existing list items first and then apply column validation using formula.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.