2

I have a SharePoint list with three date columns, Start Date, Due Date and Extended Due Date. I need to have validation set so that the Due Date is no later than 14 days from the start date and the extended due date is no later than 30 days from the start date. But the validation also needs to ignore when either column is empty. I can get the formula so that it'll validate one or the other but not both.

Trying to modify it I keep getting validation errors. Can anyone help?

My formula =IF(AND(ISBLANK [Permit End Date]>=[Permit Start Date],IF(DATEDIF([Permit Start Date],[Permit End Date],"d")<=14,True,False),False))IF(AND(ISBLANK IF([Extension End Date]>=[Permit Start Date],IF(DATEIF([Permit Start Date],[Extension End Date],"d")<=30,True,False),False)))

|improve this question
  • For clarification: You want the validation to pass if the Permit End Date is between Permit Start Date and Permit Start Date + 14, and the same with Extension End Date but with 30 days instead of 14... But you also want it to pass if either Permit End Date or Extension End Date is blank? – KGlasier Jan 16 '19 at 22:12
1

I needed clarification, but if my assumptions/interpretation was correct I think this should work:

=AND(
   DATEDIF(
      [Permit Start Date],
      [Permit End Date],
      "d"
   )>=0,
   DATEDIF(
      [Permit Start Date],
      [Permit End Date],
      "d"
   )<=14,   
   DATEDIF(
      [Permit Start Date],
      [Extension End Date],
      "d"
   )<=30,
   DATEDIF(
      [Permit End Date],
      [Extension End Date],
      "d"
   )>=0,
   OR(
      AND(
         NOT(ISBLANK( [Permit End Date] )),
         NOT(ISBLANK( [Extension End Date] ))         
      ),
      ISBLANK( [Permit End Date] ),
      ISBLANK( [Extension End Date] )
   )
)

This checks if the all of the following conditions are true:

  1. Permit End Date is greater than or equal to Permit Start Date
  2. Permit End Date is less than or equal to Permit Start Date + 14 days
  3. Extension End Date is less than or equal to Permit Start Date + 30 days
  4. Extension End Date is greater than or equal to Permit End Date
  5. Either Permit End Date or Extension End Date is empty, or neither are empty.

You can just paste the above in with the spaces in the List Validation section... SharePoint removes the spaces and puts by itself... But if you want the "space free" version:

=AND(DATEDIF([Permit Start Date],[Permit End Date],"d")>=0,DATEDIF([Permit Start Date],[Permit End Date],"d")<=14,DATEDIF([Permit Start Date],[Extension End Date],"d")<=30,DATEDIF([Permit End Date],[Extension End Date],"d")>=0,OR(AND(NOT(ISBLANK( [Permit End Date] )),NOT(ISBLANK( [Extension End Date] ))),ISBLANK( [Permit End Date] ),ISBLANK( [Extension End Date] )))

NOTE: I think the AND function returns True or False in itself, but if it doesn't you can always stick the above function into =IF( ... , TRUE, FALSE ) statement.

|improve this answer
  • Yes, this is the right track but I'm getting a "The Formula contains a syntax error or is not supported" message still. This happens if I add the IF(…,TRUE, FALSE) to it or remove it. I thought that there was a between comma missing between the ) ISBLANK. When I add a comma there and add the IF(…,TRUE, FALSE) I don't the original error but when I use the form the message "The validation formula has evaluated to an error. – Kyler Hunter Jan 18 '19 at 16:17
  • @KylerHunter Yes, sorry there was a missing comma. Can you try either of the above statements again, without the IF( ... , TRUE, FALSE )? – KGlasier Jan 18 '19 at 16:25
  • Almost there, still getting the "The Validation formula has evaluated to an error". – Kyler Hunter Jan 18 '19 at 18:31
  • @KylerHunter Please check the spelling of your columns with the spelling of my columns. According to a quick Excel check, my formula is syntactically correct. – KGlasier Jan 18 '19 at 18:36
  • Spelling checked. Nothing wrong there. I just realized I forgot to say this is for SharePoint List validation. It's always slightly tricky with transferring Excel formulas. I'll go back to poking at this. Thanks for the help. – Kyler Hunter Jan 18 '19 at 18:49

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.