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


"Ann" wrote:

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.