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:
Permit End Date
is greater than or equal to Permit Start Date
Permit End Date
is less than or equal to Permit Start Date
+ 14 days
Extension End Date
is less than or equal to Permit Start Date
+ 30 days
Extension End Date
is greater than or equal to Permit End Date
- 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.
Permit End Date
is betweenPermit Start Date
andPermit Start Date + 14
, and the same withExtension End Date
but with 30 days instead of 14... But you also want it to pass if eitherPermit End Date
orExtension End Date
is blank? – KGlasier Jan 16 '19 at 22:12