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

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.

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

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.

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

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.

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

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.

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

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

Thanks for the help!

"Elkar" wrote:

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.

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

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


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

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

Thank you, Rick

Your formula worked for me.

"Rick Rothstein (MVP - VB)" wrote:

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


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

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

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)

"Ann" wrote:

Thank you, Rick

Your formula worked for me.

"Rick Rothstein (MVP - VB)" wrote:

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


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

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

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.

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

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

"Rick Rothstein (MVP - VB)" wrote...
....
=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.

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

=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.

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

Rick