Different Renewal Dates in Each Row
Hi
I am trying to add a formula to add months to a date but each row are different months, I would enter the last renewed date and then the next column would auto update.
For example
I have used this formula =([Last Renewed]@row + 365) but this uses the same formula of the whole column also this is using days and not months.
I have the same sheet in an excel sheet and the formula is =EDATE(C4,12) but this doesn't work on smart sheets.
If someone would be able to help that would be wonderful?
Thank you
Best Answer
-
In that case I would do something like this:
=IF(Frequency@row = "Annually", DATE(YEAR([Last Renewed]@row) + 1, MONTH([Last Renewed]@row), DAY([Last Renewed]@row)), IF(Frequency@row = "Every 3 years", DATE(YEAR([Last Renewed]@row) + 3, MONTH([Last Renewed]@row), DAY([Last Renewed]@row)), IF(Frequency@row = "Every 5 years", DATE(YEAR([Last Renewed]@row) + 5, MONTH([Last Renewed]@row), DAY([Last Renewed]@row)), IF(Frequency@row = "Weekly", [Last Renewed]@row + 7, IF(Frequency@row = "Monthly", IFERROR(DATE(YEAR([Last Renewed]@row), MONTH([Last Renewed]@row) + 1, DAY([Last Renewed]@row)), DATE(YEAR([Last Renewed]@row) + 1, 1, DAY([Last Renewed]@row))), IF(Frequency@row = "Every 3 Months", IFERROR(DATE(YEAR([Last Renewed]@row), MONTH([Last Renewed]@row) + 3, DAY([Last Renewed]@row)), DATE(YEAR([Last Renewed]@row) + 1, MONTH([Last Renewed]@row) - 9, DAY([Last Renewed]@row))), IF(Frequency@row = "Every 6 Months", IFERROR(DATE(YEAR([Last Renewed]@row), MONTH([Last Renewed]@row) + 6, DAY([Last Renewed]@row)), DATE(YEAR([Last Renewed]@row) + 1, MONTH([Last Renewed]@row) - 6, DAY([Last Renewed]@row))), IF(Frequency@row = "Every 14 Months", IFERROR(DATE(YEAR([Last Renewed]@row) + 1, MONTH([Last Renewed]@row) + 2, DAY([Last Renewed]@row)), DATE(YEAR([Last Renewed]@row) + 2, MONTH([Last Renewed]@row) - 10, DAY([Last Renewed]@row)))))))))))
Answers
-
Do you have a column that has just the number of months? Would it always be an addition of years or could it be something like 14 months (or any other number of months?
-
Hi Paul
No I do't have a column for months i was hoping that when I put in a date in the last Renewed column that the next column would have a formula so that it up dates with the next date.
-
How many different lengths of time could you have? Would it be just a number of years or could it be a number of months such as 14 months?
-
Hi Paul
These are the time scales I would need
Annually, Every 14 months, Every 6 months, Every 3 months, Every 3 years, Every 5 years, Monthly & Weekly
-
In that case I would do something like this:
=IF(Frequency@row = "Annually", DATE(YEAR([Last Renewed]@row) + 1, MONTH([Last Renewed]@row), DAY([Last Renewed]@row)), IF(Frequency@row = "Every 3 years", DATE(YEAR([Last Renewed]@row) + 3, MONTH([Last Renewed]@row), DAY([Last Renewed]@row)), IF(Frequency@row = "Every 5 years", DATE(YEAR([Last Renewed]@row) + 5, MONTH([Last Renewed]@row), DAY([Last Renewed]@row)), IF(Frequency@row = "Weekly", [Last Renewed]@row + 7, IF(Frequency@row = "Monthly", IFERROR(DATE(YEAR([Last Renewed]@row), MONTH([Last Renewed]@row) + 1, DAY([Last Renewed]@row)), DATE(YEAR([Last Renewed]@row) + 1, 1, DAY([Last Renewed]@row))), IF(Frequency@row = "Every 3 Months", IFERROR(DATE(YEAR([Last Renewed]@row), MONTH([Last Renewed]@row) + 3, DAY([Last Renewed]@row)), DATE(YEAR([Last Renewed]@row) + 1, MONTH([Last Renewed]@row) - 9, DAY([Last Renewed]@row))), IF(Frequency@row = "Every 6 Months", IFERROR(DATE(YEAR([Last Renewed]@row), MONTH([Last Renewed]@row) + 6, DAY([Last Renewed]@row)), DATE(YEAR([Last Renewed]@row) + 1, MONTH([Last Renewed]@row) - 6, DAY([Last Renewed]@row))), IF(Frequency@row = "Every 14 Months", IFERROR(DATE(YEAR([Last Renewed]@row) + 1, MONTH([Last Renewed]@row) + 2, DAY([Last Renewed]@row)), DATE(YEAR([Last Renewed]@row) + 2, MONTH([Last Renewed]@row) - 10, DAY([Last Renewed]@row)))))))))))
-
Thank you so much Paul you have been really helpful :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!