Thank you everyone for your suggestions. I'd have to say that of all
the submissions, Daniel's is the one that really seems to have nailed
it. He correctly understood that simply subtracting the begin year
from the current year and dividing this number by the number of years
in the contract term does not supply the correct number of years to
add to the original year (which is what I too had originally tried).
His solution:
. Determine the number of years since the contract "begin date"
. Then divide the number of years by the length of the contract term
( 2 years )
. This gives you the number of whole terms and the current
fractional term
(ex: 2.5 - Two full renewals and 1/2 way into the current term).
. Then use the "ceiling" function to round this number up to the
nearest whole integer, which is the next whole renewal term.
. Multiply this number by the number of years in the contract term
( 2 years ).
. This gives you the number of full years to add to the original
contract begin date.
Very nice and elegant.
Thanks again for the help with this.
-Eric
On Sep 6, 6:41 am, "Daniel" <dcolarde...@free.fr> wrote:
> Try (begin date in A1) :
>
> =DATE(YEAR(A1)+CEILING(DATEDIF(A1,TODAY(),"y")/2,1)*2,MONTH(A1),DAY(A1))
>
> Regards.
>
> Daniel
>
> > -----Original Message-----
> > From: excel-macros@googlegroups.com [mailto:excel-
> > mac...@googlegroups.com] On Behalf Of Eric
> > Sent: samedi 5 septembre 2009 21:05
> > To: MS EXCEL AND VBA MACROS
> > Subject: $$Excel-Macros$$ Calculating renewal dates
>
> > I'm having a difficult time with something that sounds pretty simple on
> the
> > surface but seems to get devilishly hard (at least for me) as I look
> further into
> > it. I need to calculate contract renewal dates based on the original
> signing
> > date. For a 1 year term it's pretty straight forward but for contracts
> that have
> > a 2 year term the problem is more tricky. We also have a few contracts
> that
> > have 3 and 5 year terms so I'd need a solution that is general and can be
> > applied to any length contract term.
>
> > Here are some examples of the dates I'd need be able to calculate:
>
> > Contract term length: 2 years
>
> > Begin Date Renewal Date
> > ------------------------------------------
> > 10/25/2005 10/25/2009
> > 12/8/2003 12/8/2009
> > 2/27/2004 2/27/2010
> > 3/8/2004 3/8/2010
> > 3/27/2002 3/27/2010
> > 4/18/2006 4/18/2010
> > 4/23/1998 4/23/2010
> > 5/1/2006 5/1/2010
> > 5/6/2004 5/6/2010
> > 5/7/2004 5/7/2010
> > 5/24/2006 5/24/2010
> > 6/6/2002 6/6/2010
> > 6/25/2004 6/25/2010
> > 7/28/2004 7/28/2010
> > 8/2/2002 8/2/2010
> > 8/10/2006 8/10/2010
>
> > Any ideas or suggestions would be greatly appreciated.
>
> > -Eric
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 5,200 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
