Keep adding number until result cell is greater than reference

Hi all!

I'd really appreciate some help with a problem I have. My company has 20 or so construction workers, some of which have been with us for years. Their contracts are extended every 6, 3, or 1 month depending on our project needs.

I'd like excel to calculate the next contract extension date based on the following criteria:
Date of calculation - input in cell B2
First contract date - date the employee signed first contract
contract lenght - 1, 3 or 6 months

I have tried using this formula:

=IF(E4<=B2,DATE(YEAR(E4),MONTH(E4)+D4,DAY(E4)))

but it only makes one iteration and calculates the first next contract date which is usually earlier than the one I need:

Date of calculation = 31-Jan-2011
First contract date = 14-Aug-2010
Contract length = 3 month

RESULT= 14-Nov-2010

Is there any way to have excel keep adding the contract length until the "Next contract extension" (formula cell) is greater than the "Date of calculation" cell value?

Thanks!

Try this code - I have A1 as the start date, A2 as todayd date and B11 as contract months

=DATE(YEAR(A1),MONTH(A1)+ROUNDDOWN(DATEDIF(A1,A2,"m")/B11,0)*B11+B11,DAY(A1))

Just alter cell ref to suit your sheet.

Hope it helps

Thanks guys.

BarryTSL, your suggested formula seems to be working great except when handling end of month and leap years.

 
Start of contract = 31/12/2007
Contract length = 6
Date of calculation = 31/12/2010

I expected the output to be 30/6/2011 but it gave me 01-Jul-2011 as output.

I've managed to find a quick fix using the EDATE function and part of your formula.

The final formula for the following parameters:

Start of contract = CELL E1
Contract length = CELL F1
Date of calculation = CELL A1

=EDATE(E1;ROUNDDOWN(DATEDIF(E1;$A$1;"m")/F1;0)*F1+F1)

Once again, thank you guys very much.

I'll mark this topic as solved as soon as I finish checking the data once more.