3

I'm setting up a SharePoint page using SharePoint Online/Office 365.

For reporting purposes, I would like to ensure all entries in a particular column be exactly the same format, hence I would like to have validation in place for a text string in one of our fields.

I would like it to be YY-MM####-##. The YY being year, MM being month, #### and ##, values 0-9, and I want the hyphens to be required.

What formula would perform this?

  • Best way to start is by doing it in Excel, as all basic Functions are the same in SharePoint – Danny '365CSI' Engelman Apr 25 '17 at 18:32
  • So an example of a value in your column would be: 98-07-1234-56? Or would it be YY-MM-1998-07? – Mihail-Florin Popa Apr 25 '17 at 20:15
0

EDIT: I updated my answer to reflect the changes you've made to your question and added validation for the month as I initially omitted that part.


I will assume that the following are allowed values in your column:

  • 98-071234-56
  • 09-129876-54
  • 17-040000-00

The formula for column validation would be this - just change the YOUR_FIELD_NAME string to the actual name of your column:

=AND(NOT(ISERROR(SEARCH("??-??????-??",YOUR_FIELD_NAME,1))),LEN(YOUR_FIELD_NAME)=12,IF(ISERROR(1*CONCATENATE(MID(YOUR_FIELD_NAME,1,2),MID(YOUR_FIELD_NAME,4,6),MID(YOUR_FIELD_NAME,11,2))), FALSE,AND(MID(YOUR_FIELD_NAME,4,2)*1>0,MID(YOUR_FIELD_NAME,4,2)*1<13)))


Reference: https://techtrainingnotes.blogspot.ro/2015/10/sharepoint-column-validation-examples.html

  • Thank you very much for the help on this, I don't know if you were able to see my other comment (I can't see any trace of it) and I apologize for the changes - but I was corrected that the format needs to be "YY-MM####-##", everything in the initial answer is great, I just need the hyphen to be removed between the 5th and 7th character, and the valid range to be 0-9 for the '#' option. I tried tweaking the formula myself but this is way over my head! – Jake H. Apr 26 '17 at 19:37
  • I took another look with some fresh eyes and broke it down in my head. =AND(NOT(ISERROR(SEARCH("??-??????-??",YourFieldName,1))),LEN(YourFieldName)=12,NOT(ISERROR(1*CONCATENATE(MID(YourFieldName,1,2),MID(YourFieldName,4,6),MID(YourFieldName,11,2),MID(YourFieldName,11,2))))) Is what I wanted. Thanks for the building block! – Jake H. Apr 27 '17 at 2:45
  • I updated my answer. I also added validation for the month (00 to 12) bit because my initial formula allowed for numbers like 32 which is obviously not a month. – Mihail-Florin Popa Apr 27 '17 at 9:53
  • Perfect! Thanks again for taking a look at this - this is exactly what I was looking for! – Jake H. Apr 27 '17 at 14:22
0

You may also change the xls on the field item in SPD to achieve this.

 <xsl:value-of select="ddwrt:FormatDateTime(string(@Other_x0020_MyStuff_x0020__x0),1037, 'd/MM/yyyy h:mm:ss tt')"/>

More details here

0

You also need the validation for the month (MM) to check if the MM is validate (01~12).

The new formula is:

=AND(NOT(ISERROR(SEARCH("??-??-????-??",reportDate,1))),LEN(reportDate)=13,NOT(ISERROR(1*CONCATENATE(MID(reportDate,1,2),MID(reportDate,4,2),MID(reportDate,7,4),MID(reportDate,12,2)))), (1*CONCATENATE(MID(reportDate,4,2))>0) ,(1*CONCATENATE(MID(reportDate,4,2))<13))

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.