# Dates

#### polska2180

##### Active Member
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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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))

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

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

Barrie Davidson said:
=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.

Travis said:
Barrie Davidson said:
=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

Travis said:
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

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))

Replies
9
Views
129
Replies
4
Views
162
Replies
3
Views
120
Replies
1
Views
129
Replies
4
Views
131

1,196,514
Messages
6,015,654
Members
441,913
Latest member
Lhayden_69

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back