Renewal Date

bbloch18

Hello - I have a column for Purchased Date and a column for Renewal Date.

I want the Renewal Date to automatically populate to the following year so we know when renewal is due.  What is the best formula?



Thanks,

Barb

SmSulli

=MONTH([Purchase Date]1) + "/" + DAY([Purchase Date]1) + "/" + (YEAR([Purchase Date]1) + 1)

 

Give this a try.  This formula goes in the Renewal Date column.

Shawn

 

bbloch18

Hi Shawn - The Formula works but returns the same date as purchase date.  Is there a formula where it brings back the next year.

Purchase Date: 1/1/17

Renewal Date: 1/1/18

 

In another sheet, I am trying to do one more formula:

Date Sent: 1/1/17; F/U Date should be 21 days from Date Sent.  Is there a formula that populates the F/U Date?  Right now, we are manually entering the date that is 21 days from the Date Sent.

 

Thanks for all your help!!

Robert S.

Hello,

 

You can add days to dates with a formula such as this:

 

=[Date Column]1+21

 

This formula would add 21 days to the date in the referenced cell. If you'd like to add a year to a date, you can use this same formula layout if you'd like and add 365 days.

 

=[Date Column]1+365

 

This will work most of the time, until you reach leap years. If you want the day and month exactly the same and only change the year, you can do so with the DATE() function like this.

 

=DATE(YEAR([Purchased Date]1) + 1, MONTH([Purchased Date]1), DAY([Purchased Date]1))

 

This will take the year from the date in the referenced cell and add one year, and bring over the month and day from the same referenced cell.

J. Craig Williams

Robert's answer is spot on.

Don't use Shawn's ... it is a Text value not a Date value and won't be useful in any formula that expects a Date.

Be careful mixing type (Numbers, Text, Dates, and the rest). Often, they don't play nice with each other.

Craig