# Different Renewal Dates in Each Row

Options

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

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• edited 11/21/22
Options

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.

• ✭✭✭✭✭✭
Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

Thank you so much Paul you have been really helpful :)

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!