Date Calculation

[KonstantinN]

Dear Excel experts,

I need help on the following problem in excel involving the calculation of a date:

I do have a column (A) that shows the contract end date (01/05/2012), then there is another column (B) that shows if the contract is autorenewing (TRUE/FALSE) and another column (C) that shows the renewal term as a number in years (2).

If the contract end date (A) is smaller than TODAY() and B is TRUE, then the formula should calculate the new contract end date. The renewal term should be added until the new contract date is bigger/equal than TODAY().

Many thanks in advance,
Konstantin




[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]

[/TR]
</tbody>[/TABLE]


[TABLE="width: 212"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 341"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

i think this should work for you

=IF(AND(B2=TRUE,A2 < TODAY(),TODAY() < = DATE(YEAR(A2)+C2,MONTH(A2),DAY(A2))),DATE(YEAR(A2)+C2,MONTH(A2),DAY(A2)),"")

see here

https://www.dropbox.com/s/9jag45c8mcmmcly/contract.xlsx?dl=0

Yellow shows where the renewal would be before today and so is blank

The file will only stay on dropbox for a few days