Year Support Contracts - Automation to update end date

Rob Pritchard

We use the following formula to populate the end date of a one year contract. Namely if it starts the 1st Oct 2022 the contract expires on the 30th Sept 2023. This formula works for every other day in the month except when the renewal falls on the 1st ?

=IFERROR(DATE(YEAR([Support Contract Start Date]@row) + 1, MONTH([Support Contract Start Date]@row), DAY([Support Contract Start Date]@row) - 1), "Not on Support")

Thanks

Paul Newcome

Because you are currently subtracting 1 from the DAY. If the DAY is already the first then this outputs 0 which is an invalid value for a day within the DATE function. Try outputting the full DATE (+1 year) and then subtract 1 from the DATE as opposed to the DAY. Instead of subtracting 1 from the DAY, you want to subtract 1 from the DATE.

Basically... Move the "- 1" to the right of the closing parenthesis that is currently coming after it.

=IFERROR(DATE(YEAR([Support Contract Start Date]@row) + 1, MONTH([Support Contract Start Date]@row), DAY([Support Contract Start Date]@row)) - 1, "Not on Support")