Using excel, how do you get dates to automatically update when desired?

this is a complex question.





I'm setting up a contract excel sheet at work. Some of the contracts automatically renew themselves after X amount of time (which i'll input into a cell) from when the contract begins (also another cell input).





So lets say I have a contract which automatically renews itself every 2 years and it begins 1/1/2006. so it'll update itself 1/1/2008. after 1/1/2008, its outdated on the excel sheet. can i set up a forumula so that date will automatically update itself after every two years.





Thanks a ton!!!|||The formula below should provide a renewal date that starts at 2008, increases to 2010 from 1/1/08, then to 2012 from 1/1/10, and so on.





=DATE(2006 + 2*INT(( YEAR(TODAY()) - YEAR(B3))/2)+2,1,1)





For simplicity, you may want to break it up into a couple of steps, or just do it all at once as above.





EDIT: Forgot to say, B3 in the above equation is the beginning date (1/1/2006 in your example)|||One can accomplish this via VBA. It will require enabling macros every time the file is opened to guarantee everything is properly updated if it needs to be. Goto VB editor (ALT+F11). Goto ThisWorkbook. Locate the Open event.





Suppose the amount of time for contract is in cell B1. Suppose the date is in A1. Copy the following Code into the open event...





Dim shtnam As String


Dim xlsht As Worksheet


Dim xlrng As Range


Dim dt1 As Date


Dim dt2 As Date





shtnam = "Sheet1" '%26lt;---- name of sheet where cells are


Set xlsht = ThisWorkbook.Sheets(shtnam)


Set xlrng = xlsht.Range("A1")


dt1 = CDate(xlrng.Value)


Set xlrng = xlsht.Range("B1")


dt2 = DateAdd("yyyy", xlrng.value, dt1)


Set xlrng = xlsht.Range("A1")


If Date %26gt; dt2 Then


xlrng.Value = dt2


Application.DisplayAlerts = False


ThisWorkbook.Save


Application.DisplayAlerts = True


End If





--------------------------------------鈥?br>

This assume B1 is in years





If B1 is in months then


dt2 = DateAdd("m", xlsht.Range("B1"), dt1)


If B1 is in days then


dt2 = DateAdd("d", xlsht.Range("B1"), dt1)





Wasn't sure if you wanted to maintain the beginning date of the contract, if so just put it in a seperate cell, say B3. Aladou has devised a clever formula, this solution will work perfectly fine so long as whenever the contract renews the length of it remains fixed, I didn't make that assumption.|||Possibly. You would need to create an "IF" formula, and even then, I'm not positive it would work. Basically it would state this:





=If(today()="1/1/2006", "1/1/2008", "1/1/2006")





which means if today's date is 1/1/2006, change this cell to read 1/1/08. If today is not 1/1/06, then leave this cell to read 1/1/06.





This will solve the initial problem, however, it will not keep renewing every two years. What you're talking about would be a perpetual calculation, which, to my knowledge, isn't possible yet with Excel.