calculate anniversary of date after specified date
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 |
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 -- agarwaldvk ------------------------------------------------------------------------ agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345 View this thread: http://www.excelforum.com/showthread...hreadid=384409 |
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 |
On Mon, 4 Jul 2005 15:11:03 -0700, "slymeat" .(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 |
All times are GMT +1. The time now is 06:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com