0

I'm trying to add validation to a text column in an SPO list so that entered data only contains values from 00:00 to 23:59. I referred this article for a validation code which works fine. But it does not allow blank values. How can I accomplish this?

1
  • From the link you provided, which formula worked for you (which user's answer worked)? I will extend the same formula & provide you updated formula for this question. Jul 28, 2022 at 11:35

1 Answer 1

1

If you want to allow blank values in column validation, use formula like:

=IF(ISBLANK([Start Time]), TRUE, <current formula which worked for you goes here>)

For Example:

=IF(ISBLANK([Start Time]), TRUE, (LEN([Start Time])=5)+(MID([Start Time],3,1)=":")+(CODE(MID([Start Time],1,1))>47)+(CODE(MID([Start Time],1,1))<51)+(CODE(MID([Start Time],2,1))>47)+(CODE(MID([Start Time],2,1))<58)+(CODE(MID([Start Time],4,1))>47)+(CODE(MID([Start Time],4,1))<54)+(CODE(MID([Start Time],5,1))>47)+(CODE(MID([Start Time],5,1))<58)+IF(CODE(MID([Start Time],1,1))=50, (IF(CODE(MID([Start Time],2,1))>51, 0, 1)), 1)=11)

Update from comments:

I just tried this in my SharePoint site & it allows blank value from list forms as well:

enter image description here

6
  • Hi @Ganesh, This works fine when I'm editing or adding new item via grid view. But when I add/ edit an item using new/edit form, it does not allow blank values. Can you please check this scenario?
    – Robin Roy
    Aug 8, 2022 at 9:43
  • I just tried this in my SharePoint site & it allows blank value from list forms as well. See screenshot in my updated answer. Aug 8, 2022 at 9:53
  • I tried in an entirely new list copying the same formula. For some reason it works fine in grid view but gives the validation error message for new form.
    – Robin Roy
    Aug 11, 2022 at 11:08
  • Can you add screenshot of new form (with value you passed in form field & validation error) to your original question by editing it? Aug 11, 2022 at 11:14
  • Hi, it started working now. I had deleted the list and restored from recycle bin. After some time, new form started working as expected. Thanks.
    – Robin Roy
    Aug 11, 2022 at 15:17

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.