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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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?
 
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?
 
I'm sorry, Elkar, when I use your formula, I receive 1900 for the year
value. Any other suggestions?

Thanks for the help!
 
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
 
Thank you, Rick

Your formula worked for me.

Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
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)
 
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 <=)
Click to expand...

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
 
Rick Rothstein (MVP - VB) said:
=DATE(YEAR(B1)+(MONTH(A1)<MONTH(B1)+AND(MONTH(A1)=MONTH(B1),
DAY(A1)<DAY(B1))),MONTH(A1),DAY(A1))
Click to expand...

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.
 
=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.
Click to expand...

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

Rick
 
Back
Top