Date formula where month and day remain the same, but year will va

[Guest]

I have 3 date fields, (Excel 2003)
The Start date, (i.e. 6/1/2006). This field doesn't change once selected.
The Valuation date, (i.e. 4/21/2008) This field is a variable.
and the Next Anniversary date, where in this example, the formula should
return the value, 6/1/2008.
For the Next Anniversary date, I have tried,
=EDATE(valuation date,(ABS(YEAR(Start date)-YEAR(Valuation Date))
But I get 6/21/2008 returned.
I've also tried
=DATE(YEAR(valuation date)+1),MONTH(Start Date),DAY(Start Date)
And I get 6/1/2009 returned, a bit too far in the future.
How do I write the formula to return the nearest Policy Anniversary date,
which will always be the same month and day as the start date?
Thanks to all.

 

[Guest]

See if this works, where A1 is Start Date, and B1 is Valuation Date:

=DATE(IF(DATE(YEAR(B1),MONTH(A1),DAY(A1))<B1,YEAR(B1)+1,YEAR(B1)),MONTH(A1),DAY(A1))

HTH,
Elkar

 

[Peo Sjoblom]

Are you saying you want the day and month from the start date but the year
from the valuation date? If so use

=DATE(YEAR(Valuation_Date),MONTH(Start_Date),DAY(Start_Date))

will return 06/01/08

Instead of posting formulas that don't work describe in words what dates you
want using a few different scenarios like this

06/01/06

and the valuation date is

12/15/09


do you want

06/01/09 (which you will get using the formula I provided)

or something else?

 

[Guest]

Thank you, Peo, for the suggestion.
In the scenario you described, the next anniversary date would need to be
6/1/2010.

The start date is the first day of the policy, and the valuation date would
be a day chosen by the user to take a "snapshot" of the policy's values. The
next anniversary date will be the next time the policy's calculations would
occur, after the valuation date.

Does this help?

 

[Guest]

I'm sorry, Elkar, when I use your formula, I receive 1900 for the year
value. Any other suggestions?

Thanks for the help!

 

[Rick Rothstein \(MVP - VB\)]

Does this formula do what you want?

=DATE(YEAR(B1)+(MONTH(A1)<MONTH(B1)),MONTH(A1),DAY(A1))

If so, you will need to determine whether to use the less than symbol (<)
between the two MONTH evaluations when the valuation month/year is the same
as the policy start date's month/year, as shown in my formula, or whether to
use the less than or equal (<=) symbol instead.

Rick

 

[Guest]

Thank you, Rick

Your formula worked for me.

Does this formula do what you want?

=DATE(YEAR(B1)+(MONTH(A1)<MONTH(B1)),MONTH(A1),DAY(A1))

If so, you will need to determine whether to use the less than symbol (<)
between the two MONTH evaluations when the valuation month/year is the same
as the policy start date's month/year, as shown in my formula, or whether to
use the less than or equal (<=) symbol instead.

Rick

 

[Guest]

But as this formula takes no account of the day of B1 relative to A1 it can
still return a date earlier than B1 (or more than a year after B1 if you
change < to <=)

Elkar's formula works for me (although you might want to replace < with <=)
, or perhaps, using EDATE

=EDATE(A1,DATEDIF(A1,B1,"Y")*12+12)

 

[Rick Rothstein \(MVP - VB\)]

Does this formula do what you want?

But as this formula takes no account of the day of B1 relative to A1 it
can
still return a date earlier than B1 (or more than a year after B1 if you
change < to <=)

Good point! I think this modification to my formula handles that problem...

=DATE(YEAR(B1)+(MONTH(A1)<MONTH(B1)+AND(MONTH(A1)=MONTH(B1),DAY(A1)<DAY(B1))),MONTH(A1),DAY(A1))

Rick

 

[Harlan Grove]

=DATE(YEAR(B1)+(MONTH(A1)<MONTH(B1)+AND(MONTH(A1)=MONTH(B1),
DAY(A1)<DAY(B1))),MONTH(A1),DAY(A1))

Urgh!

=DATE(YEAR(A1)+DATEDIF(A1,B1,"Y")+(DATEDIF(A1,B1,"YD")>0),MONTH(A1),DAY(A1))

When working with anniversary dates it's ALWAYS best to add years to the
base date for the anniversaries.

 

[Rick Rothstein \(MVP - VB\)]

=DATE(YEAR(B1)+(MONTH(A1)<MONTH(B1)+AND(MONTH(A1)=MONTH(B1),

Urgh!

=DATE(YEAR(A1)+DATEDIF(A1,B1,"Y")+(DATEDIF(A1,B1,"YD")>0),MONTH(A1),DAY(A1))

When working with anniversary dates it's ALWAYS best to add years to the
base date for the anniversaries.

LOL... yeah, it was sort of a quick, top-of-the-head patch to my first
attempt.

Rick