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
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
Comments
-
=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
-
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!!
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives