Help With date. Determine the specific ranges of dates.

[raf]

Is there a formula that will determine the specific ranges of dates.

for example:
So far i have these formulas: I will enter Input: Cell A1: Sept 2, 2011

1st anniversary
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) ------- A2: Sept 2, 2012

2nd anniversary
=DATE(YEAR(A1)+2,MONTH(A1),DAY(A1)) --------A3: Sept 2, 2013

Last month of 3rd anniversary
=DATE(YEAR(A1)+3,MONTH(A1),DAY(A1)-1) -------A4: Sept30, 2014

courtesy of Smallman.


now what i want to add is Output A5 will show where the current date is belong.

For the sample above. A5 should show Sept 2 , 2013 (2nd Anniversay) since, we haven't reach Sept 30 (Last month of Anniversary) yet.

 

[Somendra Misra]

Hi Raf,

Just a blind shot, use below formula in A5:

=IF(TODAY()<=A4,A3)

Or else upload some examples with various dates situations.

Regards,