incremental years

[Guest]

i have a cell that contains a date (A1). this date forms the basis of a
lockup period that can be 2 years, 3 years, or 5 years (entered in B1). the
user will enter a date in the future (in cell C1), and a formula (in D1)
needs to be able to determine whether the funds are available. any help on
the formula would be highly appreciated. thanks!

 

[JE McGimpsey]

If I understand you correctly:

D1: =IF(C1<=DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1)),"Funded","Under-funded")

 

[Guest]

=IF(DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))<C1,"Available",Not Available")


Regards,

Peo Sjoblom

 

[Guest]

i may not have been specific enough. the lockup periods are recurring for
eternity (theoretically of course). for example, for the two year. i need
{T,T+2,T+4,T+6,...}

 

[Guest]

Thanks for posting, however, I may have not been specific enough. the lockup
periods are recurring for eternity; so every two, three, or five years there
will be one anniversary date when the money becomes available

 

[JE McGimpsey]

If the lockup periods recur for eternity, what relevance does the date
in C1 have to funding?

 

[Guest]

the date entered by the user is a question used to determine whether the
funds are available on any given year. For instance if A1 is year 2000, and
B1 is "every three years", a user may want to know whether the funds will be
availabe in 2005 (entered in C1). In this case, Excel would answer "No",
since the money is only avaible every three years (2003, 2006, 2009, 2012,
etc). I guess I could nest a bunch of "or" statements to go out 20 years or
so, but I was hoping to do it a better way.

 

[JE McGimpsey]

Ah - your "lockup period" is actually a single year of a 2, 3, or 5-year
period, right? Your question makes more sense, now. one way:

=IF(MOD(YEAR(C1)-YEAR(A1), B1)=0, "Yes","No")

 

[Guest]

that works perfect! thanks so much for your help!!!