calculate anniversary of date after specified date

[slymeat]

Is there a way of making a function determine the first anniversary of a
specified date after another and different specified date e.g. If I have a
date value of 05/06/2004 (5th June '04) in cell A1 and want to calculate (in
cell C3) the anniversary of that date that next follows a different date
specified in cell E8, say 01/11/05 (1st November '05)?

In the example above the answer in cell C3 should be 5/6/06 (5th June '06).

--
Andrew

[agarwaldvk]

Andrew

This is one of the things you can do :-

Use the "edate()" function!

finalDate = edate(yourDate, roundup(yearfrac(yourDate, yourOtherDate),0)*12)

In your example,

yourDate refers to 5th of June 2004
yourOtherDate refers to 1st of November 2005
finalDate will be 5th of June 2006


Hope this helps!


Best regards



Deepak Agarwal

[ellmcg]

My brain power's not up to it this morning, but it looks like you need to
make a user-defined function in Visual Basic Editor, probably involving a Do
While loop - to add a year to the date then check whether it is after the
'cut-off' date.

"slymeat" wrote:

> Is there a way of making a function determine the first anniversary of a
> specified date after another and different specified date e.g. If I have a
> date value of 05/06/2004 (5th June '04) in cell A1 and want to calculate (in
> cell C3) the anniversary of that date that next follows a different date
> specified in cell E8, say 01/11/05 (1st November '05)?
>
> In the example above the answer in cell C3 should be 5/6/06 (5th June '06).
>
> --
> Andrew

[Ron Rosenfeld]

On Mon, 4 Jul 2005 15:11:03 -0700, "slymeat" <[email protected].(donotspam)>
wrote:

>Is there a way of making a function determine the first anniversary of a
>specified date after another and different specified date e.g. If I have a
>date value of 05/06/2004 (5th June '04) in cell A1 and want to calculate (in
>cell C3) the anniversary of that date that next follows a different date
>specified in cell E8, say 01/11/05 (1st November '05)?
>
>In the example above the answer in cell C3 should be 5/6/06 (5th June '06).


Try this:

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

With the first specified date in A1; and the different date in A2


--ron