Automated "Valid to" date calculated from "Valid from"
I need help building a calculation in a table. The table contains conditions related to Products. Over time, conditions (such as retail price etc) change and in order to filter the conditions for further use, I set up date fields "Valid from" and "Valid to".
Instead of having the user type in a "Valid to" date, I would like to automate the entry by an IF calculation. If a record is the most recent one, the "Valid to" date should be 31.12.2999. If a new record for the same item is created, the "Valid to" date of the older record should change to the same date as entered in "Valid from" of the new record.
Important is, that the "Valid to" date is calculated from the next higher record, not the newest one.
Who can tell me how to build this calculation?