Dynamic Array - Sequence Function - End of Month

[mvbernot]

Hi All,

I'm trying to design a budget workbook that would be able to take a budget start date and a budget end date and generate the End of Month dates for that period of time. I was trying to use the sequence command but cannot figure out how to input the increment criteria to generate this list of month end dates. The periods of time could range from 6, 12, 18 or 24 months. I saw that Mr Excel had a video on adding the + sign to the EOMonth function for a range that spilled (# reference) which was extremely helpful. Is this dynamic forumula possible? Thank you for any help you can provide.

Michael

 

[mvbernot]

Thanks again!

MVB

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Eric W]

How about something like:

Book1
	H	I
2	1/1/2020	1/31/2020
3	8/1/2020	2/29/2020
4		3/31/2020
5		4/30/2020
6		5/31/2020
7		6/30/2020
8		7/31/2020
9		8/31/2020
Sheet5

Cell Formulas
Range	Formula
I2:I9	I2	=EOMONTH(EDATE(H$2,ROW()-2),0)

I still don't have the SEQUENCE function to play with, but I'd guess it would turn out to be something like:

=EOMONTH(EDATE(H2,SEQUENCE(DATEDIF(H2,H3,"m"))-1),0)

 

[Fluff]

@Eric W
Just needs a very slight tweak to get the last month
=EOMONTH(EDATE(H2,SEQUENCE(DATEDIF(H2,H3,"m")+1)-1),0)
But otherwise works fine & much simpler then my attempts

 

[mvbernot]

This works works perfectly with all my trials. Thank you, both!

 

[Fluff]

Glad we could help & thanks for the feedback.