0

Hopefully someone can help. I have a SP online List. For a date column, the user should not be able to pick a date less than the current date and also the date has to be less than 365 days. Can this be done in the Validation section of the field? If so, do you have a function for it?

2
  • List validation formula will execute when creating an item as well as when you are updating the list item (even when you are updating other form fields and not the date field where you added the column validation). Hence using formula relative to Todays date will not allow you to update the list item if previously selected date is in the past. Is this what you want (for both item creation AND update)? Commented Jan 3 at 8:52
  • Good question. In my list, I will have an additional column called '[Create Date]' that would be reference column to the Date Column in the original question which is called [Retire Date]. The Retire Date cannot be less than the 'Create Date' but cannot exceed 'Create Date' by 1 year (365 days). Commented Jan 3 at 18:36

2 Answers 2

0

See: Validating dates in SharePoint so they cannot choose a date before today's date, how would I write that formula?

=AND([DateField] >= TODAY(), [DateField] < (TODAY() + 365))

If the validation fails, the user will be unable to save the list item until they provide a valid date.

0

As per our discussion in comments section above,

List/column validation formula in SharePoint will execute when creating an item as well as when you are updating the list item (even when you are updating other form fields and not the date field where you added the column validation). Hence using formula relative to "Todays" date will not allow you to update the list item if previously selected date is in the past.

So, try using the below calculated formula in List validation (not column validation) settings (relative to [Create Date] as you mentioned in comments):

=AND([Retire Date] >= [Create Date], [Retire Date] < ([Create Date] + 365))

You have to add this formula under List Validation settings.

Follow below steps:

  1. Go to SharePoint list
  2. Click on Settings icon and select List settings
  3. Select Validation settings
  4. Add formula & user message and click Save

Notes:

  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. Use correct display name of your SharePoint columns in above formula.
  3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].

Similar threads:

  1. List validation if then syntax
  2. Validation Column Not Working

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.