ISSUE: The list validation works when adding a list entry, but when editing the same entry (with no change), the user message is displayed. Using SharePoint 2016.
I have tried numerous combinations, including not using DateDif. I'm not 100% on why it seems to prefer logic for creating a new entry (created=modified) vs an edit. I assume because created time needs to be ignored. The dates are date-only, but I did change them to date-time for some testing. The time is all 12:00:00.
User Message: The End Date cannot be less than the Begin Date AND the End Date cannot exceed 366 days from the Begin Date AND Begin Date cannot be < Created date AND Begin Date cannot exceed 60 days beyond the created date.
=AND([Begin Date]<=[End Date],
IF([Begin Date]<=[End Date],DATEDIF([Begin Date],[End Date],"d")<=366,TRUE),
OR(
AND(Created=Modified,
IF(DATE(YEAR(Created),MONTH(Created),DAY(Created))>[Begin Date],FALSE,
DATEDIF(DATE(YEAR(Created),MONTH(Created),DAY(Created)),[Begin Date],"d")+1>0
)
),
AND(Created<>Modified,
IF(Created>[Begin Date]+1,FALSE,
DATEDIF(Created,[Begin Date]+1,"d")>0
)
)
),
OR(
AND(Created=Modified,
IF(DATE(YEAR(Created),MONTH(Created),DAY(Created))>[Begin Date],FALSE,
DATEDIF(DATE(YEAR(Created),MONTH(Created),DAY(Created)),[Begin Date]+1,"d")<60)
),
AND(Created<>Modified,
IF(Created>[Begin Date]+1,FALSE,
DATEDIF(Created,[Begin Date]+1,"d")<60
)
)
)