I need to add validation to a single line of text column in a SharePoint list. For other reasons, the column must be a single line of text, but it needs to be formatted like a date. The column must follow the following format: xx/xx/xxxx>>xx/xx/xxxx where the x's equal the numbers of the date. I am stumped on this one, any help would be greatly appreciated. Thanks so much.
2
-
Is the formatted output a valid date? Or it will accept values like 99/99/9999?– Matiur RahmanCommented Mar 2, 2021 at 15:57
-
the output is a valid date rage: 03/02/2021>>03/28/2021– seaandteaCommented Mar 2, 2021 at 20:07
Add a comment
|
1 Answer
Since it's a very narrow range, verifying the month, day, and year is suffix.
=IF(LEN(TRIM(FormatLikeDate))=10,IF(AND(MID(TRIM(FormatLikeDate),3,1)="/",MID(TRIM(FormatLikeDate),6,1)="/"),IF(AND(INT(MID(TRIM(FormatLikeDate),1,2))=3,AND(INT(MID(TRIM(FormatLikeDate),4,2))>1,INT(MID(TRIM(FormatLikeDate),4,2))<29),INT(MID(TRIM(FormatLikeDate),7,4))=2021),TRUE,FALSE),FALSE),FALSE)
answered Mar 2, 2021 at 21:15
