0

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? Mar 2, 2021 at 15:57
  • the output is a valid date rage: 03/02/2021>>03/28/2021
    – seaandtea
    Mar 2, 2021 at 20:07

1 Answer 1

0

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)

enter image description here

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.