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:
but it only makes one iteration and calculates the first next contract date which is usually earlier than the one I need:=IF(E4<=B2,DATE(YEAR(E4),MONTH(E4)+D4,DAY(E4)))
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?Date of calculation = 31-Jan-2011
First contract date = 14-Aug-2010
Contract length = 3 month
RESULT= 14-Nov-2010
Thanks!
Bookmarks