1

I created a form with fields to enter a start date and an end date. The form successfully rejects any dates that fall on or between a specific date range: 03/05/2023 to 10/05/2023 (dd/mm/yyyy). But, it will accept start and end dates that include the restricted date range as long as the start and end dates are outside the restricted date range.

For example, the date range 02/05/2023 to 11/05/2023 would be accepted by the form even though it includes the restricted date range 03/05/2023 to 10/05/2023.

This is how I currently work it:

=(FirstDayOf Required < DateTime.Today) or 
 (FirstDayOf Required.Date = DateTime.Today) or 
 (FirstDayOf Required >= "12/02/2023" and FirstDayOf Required <= "12/17/2023") or
 (FirstDayOf Required >= "03/28/2023" and FirstDayOf Required <= "04/16/2023") or
 (FirstDayOf Required >= "06/26/2023" and FirstDayOf Required <= "06/30/2023") or
 (FirstDayOf Required >= "05/20/2023" and FirstDayOf Required <= "05/20/2023") or 
 (FirstDayOf Required >= "03/11/2023" and FirstDayOf Required <= "03/18/2023") or 
 (FirstDayOf Required >= "03/04/2023" and FirstDayOf Required <= "03/05/2023")

How can I reject start and end dates within my restricted date range, and also ones start and end dates outside the restricted date range but whose date range includes my restricted date range?

0

1 Answer 1

0

Custom Error Logic

Your custom error logic should verify not only that neither FirstDayOf nor LastDayOf fall within the excluded date range, but also that no combination of dates creates a range that includes the excluded range, and that FirstDayOf precedes LastDayOf.

Formula

The formula assumes the excluded date range to be 03/05/2023 to 10/05/2023

# Show Custom Error for 'Required' field when:

=(FirstDayOf < DateTime.Today) or
 (FirstDayOf >= "03/05/2023" and FirstDayOf <= "10/05/2023") or
 (LastDayOf <= FirstDayOf) or
 (LastDayOf >= "03/05/2023" and LastDayOf <= "10/05/2023") or
 (FirstDayOf < "03/05/2023" and LastDayOf >= "03/05/2023")
Ref Notes
1 FirstDayOf must be in the future
2 FirstDayOf can't fall within 03/05/2023 to 10/05/2023
3 LastDayOf must be after FirstDayOf
4 LastDayOf can't fall within 03/05/2023 to 10/05/2023
5 FirstDayOf to LastDayOf can't include 03/05/2023
0

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.