0

I have a column validation being used for one of the Date Fields called Holiday Date. Below is the validation formula:

=OR(Holiday=DATEVALUE("8/3/2018"),Holiday=DATEVALUE("10/8/2018"))

So i am only allowing users to enter the above two dates mentioned in the formula, if they enter anything else then it throws an error. The issue is, I have kept this field optional OOTB but because of column validation, if I leave it blank, it shows me the column validation error.

Is there anyway we can allow a date field to be left empty even if column validation is in use for the same or no?

Thanks in advance.

1

Add a condition in your OR function to check for blank value:

=OR(Holiday=DATEVALUE("8/3/2018"),Holiday=DATEVALUE("10/8/2018"), Holiday="")

  • I got one more question for you. I have a start date field where I am using custom validation to only allow users to enter weekend dates. here it is: =OR(AND(NOT(ISBLANK([Start Date])),WEEKDAY([Start Date],2)=6),WEEKDAY([Start Date],2)=7). I wanted to account for null value here as well for Start Date field but could not get it to work. Any Suggestions? – mdevm Jul 17 '18 at 13:11
  • Just remove your NOT() function and the AND() function as well. As it is currently written, your formula will only pass validation if Start Date is not blank, and is the 6th or 7th weekday (Saturday or Sunday). You want it to pass if Start Date is blank, OR is the 6th or 7th weekday. Go ahead and try to modify your formula with that advice, and let me know if you get it working right. :) – C. Dennett Jul 17 '18 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.