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 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

2
  • 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
    yesterday
  • I just tried this in my SharePoint site & it allows blank value from list forms as well. See screenshot in my updated answer. yesterday

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.