Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Renewal Date

bbloch18 ✭✭✭
edited 12/09/19 in Archived 2017 Posts

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?





  • 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.



  • bbloch18
    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.
    Robert S. Employee



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


This discussion has been closed.