Dates

[polska2180]

I have dates going back to the 80's. These dates represent the date the customer signed an agreement with us. The agreements are 3 years at a time and renew for a like 3 year term. What I would like is an equation that would advance the date to the next time an agreement would renew so if I have 01/01/99 it would expire/renew on 01/01/08 or if I have 01/01/01 it would renew/expire 01/01/07.

Thanks for the help

 

[Travis]

Assuming the original agreement date is in A1, I think this would work.

=DATE(YEAR(TODAY())+3-MOD(YEAR(TODAY())-YEAR(A1),3),MONTH(A1),DAY(A1))

 

[Barrie Davidson]

=DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))

Assumes that you're adding 3 years. Change as required.

 

[Travis]

=DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))

Assumes that you're adding 3 years. Change as required.

That would only find the first renew date. I think the opp wants to take a date several years in the past and increment it in 3 year blocks until a date in the future is obtained. To find the next renew date.

 

[polska2180]

ADVERTISEMENT

=DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))

Assumes that you're adding 3 years. Change as required.

That would only find the first renew date. I think the opp wants to take a date several years in the past and increment it in 3 year blocks until a date in the future is obtained. To find the next renew date.

that is correct

 

[polska2180]

Assuming the original agreement date is in A1, I think this would work.

=DATE(YEAR(TODAY())+3-MOD(YEAR(TODAY())-YEAR(A1),3),MONTH(A1),DAY(A1))

this is great thanks

 

[barry houdini]

If your date in A1 is 30th December 2002 is the required result 30th December 2005 (given today's date, 4th December 2005)? - the above formula will give 30th December 2008.

Using EDATE (which requires Analysis ToolPak) I'd use

=EDATE(A1,(INT(DATEDIF(A1,TODAY(),"y")/3)+1)*36)

assumes that A1 is equal to or less than TODAY()

or without Analysis ToolPak

=DATE(YEAR(A1)+(INT(DATEDIF(A1,TODAY(),"y")/3)+1)*3,MONTH(A1),DAY(A1))