I am creating a form with two date fields, Start Date and Expiration Date. I need to make sure that the Expiration Date is within 365 days of the Effective Date. IE Start Date 1/1/2019 Expiration Date 12/31/2019 is VALID, but Start Date 1/1/2019 Expiration Date 2/28/2020 is invalid. I tried creating a validation range where minimum value was set to Effective Date, then tried using the following formula for Maximum Value:
=fn-LessThanOrEqual(fn-DateDiffDays(Start Date, Expiration Date), 365)
Unfortunately, this always came back as false, no matter what date was entered.
I also tried creating a validation rule using this formula:
Solved! Go to Solution.
You're formula is correctly setup, but you should just have to wrap the 365 in quotes so it will look like the below:
Using this formula in a calculated value to show whether it evaluates correctly:
This is correct, I did need to add quotations around the 365 as shown above. However, when making a validation rule in my version of Nintex forms, I also had to change from lessThanOrEqual to greaterThan. The condition states that the control is INVALID if... I was writing my control backwards, so after adding quotations, it would only validate if the expiration date was greater than one year from the start date. Full correct formula in the validation rule control box:
Also, make ABSOLUTELY SURE that you use "Named Controls" and not "Item Properties" when you are constructing this formula, or it will not work. Not only will it not work, it will do all kinds of weird shit. Wasted hours on that on another form today.
Good call out on that one. Item properties will be referencing the list item properties, which if the form has not been saved, will not be populated. If you're using Item properties, you'll only want to use them on the 'Edit Form' options where the form has already been submitted. The 'Named Controls' will be pulling what's in the form rather than the data stored to the list.