Need help w/ formula to calculate next X-month increment from a date based on today's date

[ChrisLaR]

I'm trying to set up a formula that will tell me when the next date is based on a specific increment of time from an "origin" date.

For example, let's say I have a contract start date of 1/1/2020 and the contract renews on 6-month increments, I would want the result shown right now to be 1/1/2023. However if today were April 3, 2022, I would want the result shown to be 7/1/2022.

I also would ideally have a way to modify it for 6, 12, and 1 month intervals. So I have this formula right now that works as desired for 12 month intervals:

if(I3="12-Month Auto",edate(F3,(datedif(F3,today(),"y")+1)*12)

But i cannot for the life of me figure out how to modify it for these alternative intervals. I've dissected how the edate & datedif formulas work, but the best I can do is understand why changing the 12 in the formula above to a 6 doesn't work. I cannot figure out how to modify it, or what alternative formula I need to get the 6-month (or 3-month, 9-month, 1-month) intervals to work.

Any thoughts on how to make this work? TY!

 

[etaf]

did the solution here on a different forum NOT work - seems to work for me

https://www.reddit.com/r/excel/comments/xwhk25/_/irgezis

work, shows 6 mths and 3 months

I year =DATE(YEAR(TODAY())+(TODAY()>DATE(YEAR(TODAY()),MONTH(F3),1)),MONTH(F3),1)
6mth = =EDATE(F3,(ROUNDDOWN(DATEDIF(F3,TODAY(),"m")/6,0)*6)+6)
3mth = =EDATE(F3,(ROUNDDOWN(DATEDIF(F3,TODAY(),"m")/3,0)*3)+3)

Book2
	A	B	C	D	E	F	G	H	I
1
2				renewal
3				12/9/22		3/9/20			3-Month Auto
4				2/10/23		8/10/19			6-Month Auto
5				4/18/23		4/18/20			12-Month Auto
6				4/19/23		4/19/20			12-Month Auto
7
8
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=IF(I3="12-month Auto",DATE(YEAR(TODAY())+(TODAY()>DATE(YEAR(TODAY()),MONTH(F3),DAY(F3))),MONTH(F3),DAY(F3)),IF( I3="6-month Auto",EDATE(F3,(ROUNDDOWN(DATEDIF(F3,TODAY(),"m")/6,0)*6)+6),IF( I3="3-month Auto",EDATE(F3,(ROUNDDOWN(DATEDIF(F3,TODAY(),"m")/3,0)*3)+3),”Col I Error” )))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Reddit
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[ChrisLaR]

Ah! Sorry I didn't remember the crossposting rule from when I first signed up here.

Confirming that YES the above solution does work as I hoped it would.

Appreciate the help and attention here!