1

I want to write 2 formula to validate below conditions:

I have 3 columns in SharePoint list:

  1. Date of Booster (Date field)
  2. Is resident in Hospice? (Yes/No Field)
  3. Date Admission at Hospice (Date Field)

1st formula: User must enter a date > 9/1/2021 to validate the date must be greater than 9/1/2021 in [Date of Booster] field

2nd formula : If user selects Yes in [Is resident in Hospice], they must be enter the admission date which it needs to be later than 9/1/2021 in [Date Admission at Hospice]

1 Answer 1

0

For first condition, use column level validation on "Date of Booster" field. Use formula:

=[Date of Booster] > DATE(2021,9,1)

For second condition, use list level validation. Use formula:

=IF([Is resident in Hospice?], IF(ISBLANK([Date Admission at Hospice]), false, IF([Date Admission at Hospice] > DATE(2021,9,1), true, false)), true)

Reference of similar thread for list validation steps: Validation Column Not Working


Note:

  1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

Official Documentations:

  1. Calculated Field Formulas.
  2. IF function.
7
  • Hello Ganesh, the first formula is working, but the 2nd formula returned with syntax error. Nov 9, 2021 at 21:07
  • Where are you adding second formula? Remember you have to add it to list validation settings and not column validation settings of "[Date Admission at Hospice]" column. Also, make sure you are using correct names of your columns. Nov 10, 2021 at 1:59
  • Yes, I have added 2nd formula in List Validation Setting rather than column validation level. After I have added 2nd formula in List Validation Setting successfully, it crashed with 1st formula in [Date of Booster] field. It returned "evaluated error " when I entered the date >9/1/2021 Nov 10, 2021 at 17:08
  • What were the values of other two columns when this error occurred? Nov 10, 2021 at 17:34
  • Hello Ganesh, for 1st formula =[Date of Booster] > DATE(2021,9,1) , this [Date of Booster] is an optional field which this field can be blank. I need help to have a revise formula for to validate [Date of Booster] when user enter the date. User could not save the entry without entering date because I have used =[Date of Booster] > DATE(2021,9,1) Nov 12, 2021 at 17:08

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.