1

New to Sharepoint trying to find my way:)

I have the following structure : StartDate1,EndDate1,StartDate2,EndDate2 StartDate1 and EndDate1 are mandatory fields. StartDate2 and EndDate2 are not. I’m trying to create a list validation where if Start date 2 contains data EndDate2 cannot left Blank (and vice verca) and at the same time for both Start End Date columns , End dates are not earlier than Start Dates

Any assistance will be highly appreciated Thanks all!

1

From my understanding you have four columns StartDate1, StartDate2, EndDate1, and EndDate2. I'll shorten these to S1, S2, E1, and E2respectively. The final equation will use the full names at the bottom of the post. If the validation doesn't work, please let me know.

To put list validation on, you have to go to List Settings > Validation Settings as per this image below. Column validation doesn't allow for multi-column validation, AFAIK.

enter image description here

S1 and E1 are always required.

S2 and E2 are only occasionally required. If one is filled out, the other must also be filled.

Sn and En must be in chronological order (ie S1 < E1)

For this we can take S1 and E1 and mark them required in the column definition to save some additional logic in the item validation.

List validation triggers when an expression equates to FALSE.

To check if an item is greater than we can ask S1 < E1 AND S2 < E2 If those both hold up, we get a true return. If not, we get a false return. In SharePoint this turns into

=AND(StartDate1 < EndDate1, StartDate2 < EndDate2)

Now we also need the conditionally required columns, S2 and E2. Check out the table below. We want to evaluate to FALSE.

S2 Present | E2 Present || Valid?
---------------------------------
     F     |     F      ||   T
     F     |     T      ||   F
     T     |     F      ||   F
     T     |     T      ||   T

We need a TRUE for both empty, and a TRUE for both full to get the proper FALSE values. So if we have (S2 AND E2) OR (NOT S2 AND NOT E2) we'd get...

=OR(
   NOT(
      AND(
         ISBLANK(StartDate2),
         ISBLANK(EndDate2)
      )
   ),
   AND(
      ISBLANK(StartDate2),
      ISBLANK(EndDate2)
   )
)

Now we need both conditions to be TRUE in order to pass the validation, so that implies an AND condition. We can add the bottom, longer, condition to the previous AND condition. The final validation string should be:

=AND(
   StartDate1 < EndDate1, 
   StartDate2 < EndDate2,
   OR(
      AND(
         NOT(ISBLANK(StartDate2)),
         NOT(ISBLANK(EndDate2))
      ),
      AND(
         ISBLANK(StartDate2),
         ISBLANK(EndDate2)
      )
   )
)
|improve this answer|||||
  • Thanks for the Reply KGlacier as well as for the logic explained.Unfortunately When S2 is blank and only E2 has value , validation passes. Please check again if possible.Thanks again. – LoukasTS Oct 19 '19 at 4:40
  • @LoukasTS Sorry, I put the NOT operator at the wrong spot. Please try again. I’ll update the description in the morning. – KGlasier Oct 19 '19 at 4:51
  • KGlasier, works perfect as it should be!!Thanks for helping – LoukasTS Oct 19 '19 at 15:13
  • Great! Don’t forget to mark it as an answer so others know :) @LoukasTS – KGlasier Oct 19 '19 at 15:16

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.