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]

I tried that formula without the + before each range and got an error. I don't even know why adding the plus would work?

See this thread.

 

[Jeffrey Mahoney]

I guess you could just add formulas in the header cells

Book2
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1				2024-06-30	2024-07-31	2024-08-31	2024-09-30	2024-10-31	2024-11-30	2024-12-31	2025-01-31	2025-02-28	2025-03-31	2025-04-30	2025-05-31	2025-06-30	2025-07-31
2	Max Date Projected:	2025-07-31
3	Base Date:	2024-06-01
Sheet3

Cell Formulas
Range	Formula
G1	G1	=EOMONTH(E3,0)
H1:T1	H1	=EOMONTH(G1,1)

 

[Jeffrey Mahoney]

The "+" character is a backwards-compatible effort by Microsoft to accommodate formula entry by people who were former Lotus users, in long ago days when Lotus was the dominant spreadsheet program and MS was building their customer base.

Lotus, SMH. I guess whatever works

And then using DateDif, also a Lotus leftover.

 

[ricafonyat]

Lotus, SMH. I guess whatever works

And then using DateDif, also a Lotus leftover.

Unfortunately the Datedif function do not exist in brazilian excel version

 

[ricafonyat]

I guess you could just add formulas in the header cells

Book2
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1				2024-06-30	2024-07-31	2024-08-31	2024-09-30	2024-10-31	2024-11-30	2024-12-31	2025-01-31	2025-02-28	2025-03-31	2025-04-30	2025-05-31	2025-06-30	2025-07-31
2	Max Date Projected:	2025-07-31
3	Base Date:	2024-06-01
Sheet3

Cell Formulas
Range	Formula
G1	G1	=EOMONTH(E3,0)
H1:T1	H1	=EOMONTH(G1,1)

In fact, It is necessary reference the content of Col A and Col B

 

[Cubist]

Unfortunately the Datedif function do not exist in brazilian excel version

DATEDIF won't show up in the suggestion drop-down, but it's available. It's one of the few hidden functions. Did you try the formula in post#10?

 

[Jeffrey Mahoney]

DATEDIF won't show up in the suggestion drop-down, but it's available.

Same for me. It doesn't look like it's an Excel Function, but you can use it.

 

[ricafonyat]

DATEDIF won't show up in the suggestion drop-down, but it's available. It's one of the few hidden functions. Did you try the formula in post#10?

Yes, I tried !
But it does not work

 

[Cubist]

Yes, I tried !
But it does not work

Are the dates in col A actual date or are they text? Check with =ISNUMBER(A1:A4)
Does it return TRUE or FALSE?

 

[ricafonyat]

Are the dates in col A actual date or are they text? Check with =ISNUMBER(A1:A4)
Does it return TRUE or FALSE?

They are dates