0

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
)
       )
)

1 Answer 1

0

The issue all hinges on comparing a date (without time) to the Created or Modified dates that have time. To avoid the issue, add 1 day to the date without time and change the comparison operator accordingly. In my case, the final validation formula is:

    =AND([Begin Date]<=[End Date],[End Date]<=[Begin Date]+366,[Begin Date]+1>Created,[Begin Date]+1<Created+60)

Hopefully this will help someone else.

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.