Forum Discussion
Past Date Restriction
Hello,
I have a complaints register which has a Date Logged and Date Resolved. I have restricted the Date Logged column for users not to enter dates in the past. I used the formula =[Date Logged]>=TODAY()
But this is causing a problem because say a user logs an issue on Dec 1st, the issue may be resolved on Dec 5th. So when I save the modified entry, it fails because of the Date Logged validation.
How can I get around this problem?
Samuel_Kamaloni Can you try list validation formula like this:
=IF([Created]=[Modified],[Date Logged]>=TODAY(),TRUE)You have to use above formula in list validation settings and not in column validation settings.
Check more details in similar thread at: SharePoint list column validation - if status is a certain value, require a date in another column
Edit:
I just tested this in our SharePoint site. Using above formula will allow you to update the list item. However, user might change the value of Date Logged column to any past dates. So, you may need to keep the copy of date value when item was created in another column and add one more condition in formula so that Date Logged column values should be equal (or greater than, as per your requirements) to value of another copied column.
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
Samuel_Kamaloni Can you try list validation formula like this:
=IF([Created]=[Modified],[Date Logged]>=TODAY(),TRUE)You have to use above formula in list validation settings and not in column validation settings.
Check more details in similar thread at: SharePoint list column validation - if status is a certain value, require a date in another column
Edit:
I just tested this in our SharePoint site. Using above formula will allow you to update the list item. However, user might change the value of Date Logged column to any past dates. So, you may need to keep the copy of date value when item was created in another column and add one more condition in formula so that Date Logged column values should be equal (or greater than, as per your requirements) to value of another copied column.
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
Please read my above response carefully, you have to use the formula in list validation settings and not in column validation settings.
You can check the link in my above response for the steps to add list validation formula.
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
When you apply column or list validation using SharePoint list formulas, those are applied to new item creation as well as existing item update operations.
Unfortunately, these is no way to define such formulas only for either of operations mentioned above.
So, you can customize the SharePoint online list forms using Power Apps and add validations using Power Apps formulas. Check: Customize a Microsoft Lists or SharePoint form by using Power Apps
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
