Strict Text Format

Question

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, Apr 25 '17 at 20:15

Mihail · Accepted Answer · 2017-04-27T11:26:07.847

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

edited Apr 27 '17 at 11:26

answered Apr 25 '17 at 20:47

Mihail

2,099
13
19

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 02: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 Apr 27 '17 at 09: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

score 0 · Answer 2 · answered Apr 26 '17 at 04:47

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

answered Apr 26 '17 at 04:47

garvon-77

589
10
34

score 0 · Answer 3 · answered Apr 26 '17 at 05:31

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

Strict Text Format

3 Answers3