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?

"Peo Sjoblom" wrote:

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(S tart_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?



--
Regards,

Peo Sjoblom


"Ann" wrote in message
...
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.