Different Renewal Dates in Each Row

Vikki Cornwall

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

Paul Newcome

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

Paul Newcome

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?

Vikki Cornwall

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.

Paul Newcome

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?

Vikki Cornwall

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

Paul Newcome

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

Vikki Cornwall

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

Paul Newcome

Happy to help. 👍️