Specific Dates for auto renewal cases

[ishpahuja]

Hi friends
I am looking to develop a formula that will return the next renewal date after one and then each subsequent year. I tried a couple of ways but couldn't finalize it.
For e.g. if the start date is 01-May -2019 then the auto-renewal date should return 30-Apr-2020. However, if today is more than 30-Apr-2020 , then the auto-renewal date should return 30-Apr-2021 and this is how it should continue to give next year date based on today's date.

Can you please advise how can I develop a formula for this?

Thanks in advance.

 

[ishpahuja]

Hi guys, can you please advise?

 

[alansidman]

Book11
	A	B
1	Date	Renewal
2	5/1/2019	4/30/2020
3	5/1/2020	5/1/2021
4	5/1/2021	5/1/2022
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IF(A2>4/30/20,A2+365,4/30/20)

 

[etaf]

ignore

 

[ishpahuja]

Book11
	A	B
1	Date	Renewal
2	5/1/2019	4/30/2020
3	5/1/2020	5/1/2021
4	5/1/2021	5/1/2022
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IF(A2>4/30/20,A2+365,4/30/20)

Thanks so much for responding. However, I was looking for something else. The situation is like this:
Say today = 25 Nov 2020

Input Date	Result that I am looking for
18-May-2018	17-May-2021
18-May-2019	17-May-2021
18-May-2020	17-May-2021
25-Nov-2020	24-Nov-2021
01-Jan-2021	31-Dec-2021

The situation is that for e.g. if one of my clients signed up on 18-May-2018, his contract gets renewed every year, so it will give 17-May-2019 if today is before 17-May-2019. After 17-May-2019 and before 17-May-2020, it should say 17-May-2020, and so and so forth. Every year, as the contract renwes, I'd like to know what is the next renewal date?
I hope this helps explain the challenge better.

 

[etaf]

does this work ?
=IF(TODAY()<DATE(YEAR(A13),MONTH(A13),DAY(A13)),DATE(YEAR(A13)+1,MONTH(A13),DAY(A13)-1),DATE(YEAR(TODAY())+1,MONTH(A13),DAY(A13)-1))

Book1
	A	B	C	D
12	Input Date	Result that I am looking for		Formula
13	18-May-18	17-May-21		17-May-21
14	18-May-19	17-May-21		17-May-21
15	18-May-20	17-May-21		17-May-21
16	25-Nov-20	24-Nov-21		24-Nov-21
17	1-Jan-21	31-Dec-21		31-Dec-21
18	2-May-21			1-May-22
19	2-Nov-22			1-Nov-23
Sheet2

Cell Formulas
Range	Formula
D13:D19	D13	=IF(TODAY()<DATE(YEAR(A13),MONTH(A13),DAY(A13)),DATE(YEAR(A13)+1,MONTH(A13),DAY(A13)-1),DATE(YEAR(TODAY())+1,MONTH(A13),DAY(A13)-1))

 

[ishpahuja]

does this work ?
=IF(TODAY()<DATE(YEAR(A13),MONTH(A13),DAY(A13)),DATE(YEAR(A13)+1,MONTH(A13),DAY(A13)-1),DATE(YEAR(TODAY())+1,MONTH(A13),DAY(A13)-1))

Book1
	A	B	C	D
12	Input Date	Result that I am looking for		Formula
13	18-May-18	17-May-21		17-May-21
14	18-May-19	17-May-21		17-May-21
15	18-May-20	17-May-21		17-May-21
16	25-Nov-20	24-Nov-21		24-Nov-21
17	1-Jan-21	31-Dec-21		31-Dec-21
18	2-May-21			1-May-22
19	2-Nov-22			1-Nov-23
Sheet2

Cell Formulas
Range	Formula
D13:D19	D13	=IF(TODAY()<DATE(YEAR(A13),MONTH(A13),DAY(A13)),DATE(YEAR(A13)+1,MONTH(A13),DAY(A13)-1),DATE(YEAR(TODAY())+1,MONTH(A13),DAY(A13)-1))

Thank you so much, etaf. This is working

 

[etaf]

you are welcome