Hi all,
I have a contracts spreadsheet containing all our suppliers that contain contract renewal dates, contract duration and monthly cost. I'm trying to work out a formula to display the cost to terminate a supplier 18 months from now which takes into consideration the remaining term of the contract and therefore whether that contract will also need to be renewed within the 18 months.
As an example, if todays date is 01/01/20, a supplier has a monthly cost of $1000, a contract duration of 60 months and a renewal date of 01/01/2021, the cost to terminate that supplier between now and 02/07/2022 (18 months time) would be $12,000 + $60,000.
If the renewal date in the same example was instead 01/01/23, then the cost to terminate would be $18,000 (no renewal).
Any help would be massively appreciated - I think I need to use some IF statements and an array to find the possible renewal dates in the range but I just can't work out how to do it!
Many thanks,
BD
I have a contracts spreadsheet containing all our suppliers that contain contract renewal dates, contract duration and monthly cost. I'm trying to work out a formula to display the cost to terminate a supplier 18 months from now which takes into consideration the remaining term of the contract and therefore whether that contract will also need to be renewed within the 18 months.
As an example, if todays date is 01/01/20, a supplier has a monthly cost of $1000, a contract duration of 60 months and a renewal date of 01/01/2021, the cost to terminate that supplier between now and 02/07/2022 (18 months time) would be $12,000 + $60,000.
If the renewal date in the same example was instead 01/01/23, then the cost to terminate would be $18,000 (no renewal).
Any help would be massively appreciated - I think I need to use some IF statements and an array to find the possible renewal dates in the range but I just can't work out how to do it!
Many thanks,
BD
