Automatically work out Contract Expiry Dates

Steve Bertram

Hi,

i'm trying to write a formula that will automatically work out the expiry date of a contract any given number of years in the future.

If the 'OSD' is the start date and the contract runs for 8 years this is fairly simple e.g. OSD - 01/10/12 then 8 year anniversary - 01/10/20 (UK date format) using formula:

=IF(ISDATE(OSD@row), DATE(YEAR(OSD@row) + [Initial Contract Period in Years]@row, MONTH(OSD@row), DAY(OSD@row)))

However the difficulty comes when I try to shorten the contract by length 1 day (because strictly speaking that's when the contract will expire).

Any help on how to make the above formula work or indeed any suggestions on a different approach would be most welcome.

Kind Regards

Steve

Paul Newcome

Try pulling the entire date and then subtracting 1 from it.

=IF(ISDATE(OSD@row), DATE(YEAR(OSD@row) + [Initial Contract Period in Years]@row, MONTH(OSD@row), DAY(OSD@row)) - 1)

Paul Newcome

Try pulling the entire date and then subtracting 1 from it.

=IF(ISDATE(OSD@row), DATE(YEAR(OSD@row) + [Initial Contract Period in Years]@row, MONTH(OSD@row), DAY(OSD@row)) - 1)

Steve Bertram

Thanks for your help Paul,

I was so close, yet so far.

Kind Regards

Steve

Paul Newcome

Happy to help. 👍️