Maximum End of Month Date Array Formula

[ricafonyat]

What array formula (CTRL SHIFT ENTER) should I use to calculate the largest projected date in cell E2 considering the number of months shown in column B and just referencing columns A and B in the formula?

Thanks in advance
Best Regards

 

[Cubist]

Can you show what's on your sheet and the formula you entered?

 

[Fluff]

Unfortunately the Datedif function do not exist in brazilian excel version

It's called DATADIF in Brazilian.

 

[ricafonyat]

Try.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Date	# Months
2	1/15/2024	12		Base Date	6/1/2024
3	12/15/2024	5
4	1/10/2025	6		Sequence	6/30/2024	7/31/2024	8/31/2024	9/30/2024	10/31/2024	11/30/2024	12/31/2024	1/31/2025	2/28/2025	3/31/2025	4/30/2025	5/31/2025	6/30/2025	7/31/2025
5
Sheet3

Cell Formulas
Range	Formula
E4:R4	E4	=EOMONTH(E2,SEQUENCE(1,DATEDIF(E2,MAX(EDATE(+A2:A4,+B2:B4)),"m")+1,0))
Dynamic array formulas.

I tried the formula below and it works.

=EOMONTH($E$3;SEQUENCE(1;12*(YEAR(MAX(EOMONTH(+A2:A4;+B2:B4)))-YEAR(MAX(EOMONTH(E3;0))))+MONTH(MAX(EOMONTH(+A2:A4;+B2:B4)))-MONTH(E3)+1;0))

 

[Eric W]

Another option:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Num of Months
2	1/15/2024	12		Max Date Projected (End of Month)	7/31/2025
3	12/15/2024	5		Base Date	6/1/2024
4	1/10/2025	6
5				Sequence	6/30/2024	7/31/2024	8/31/2024	9/30/2024	10/31/2024	11/30/2024	12/31/2024	1/31/2025	2/28/2025	3/31/2025	4/30/2025	5/31/2025	6/30/2025	7/31/2025
Sheet3

Cell Formulas
Range	Formula
E2	E2	=MAX(EOMONTH(EDATE(+A2:A4,+B2:B4),0))
E5:R5	E5	=LET(f,EOMONTH(E3,SEQUENCE(,100,0)),FILTER(f,f<=E2))
Dynamic array formulas.