Automatically work out Contract Expiry Dates

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
Best Answer
-
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)
Answers
-
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)
-
Thanks for your help Paul,
I was so close, yet so far.
Kind Regards
Steve
-