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

 

[Fluff]

A bit clunky, but how about

+Fluff New.xlsm
	H	I
1
2	01/01/2020	31/01/2020
3	01/08/2020	29/02/2020
4		31/03/2020
5		30/04/2020
6		31/05/2020
7		30/06/2020
8		31/07/2020
9		31/08/2020
10
11
Master

Cell Formulas
Range	Formula
I2:I9	I2	=EOMONTH(DATE(YEAR(H2),SEQUENCE(MONTH(H3)-MONTH(H2)+1,,MONTH(H2),),1),0)
Dynamic array formulas.

 

[mvbernot]

Thank you so much! I can't wait to give this a try!! WOW.......no "clunky" that I can see!!

 

[Fluff]

Don't bother
a) It won't work across years
b) I have simplified it

Try

+Fluff New.xlsm
	H	I
1
2	01/01/2020	31/01/2020
3	01/08/2021	29/02/2020
4		31/03/2020
5		30/04/2020
6		31/05/2020
7		30/06/2020
8		31/07/2020
9		31/08/2020
10		30/09/2020
11		31/10/2020
12		30/11/2020
13		31/12/2020
14		31/01/2021
15		28/02/2021
16		31/03/2021
17		30/04/2021
18		31/05/2021
19		30/06/2021
20		31/07/2021
21		31/08/2021
22
Master

Cell Formulas
Range	Formula
I2:I21	I2	=EOMONTH(SEQUENCE(DATEDIF(H2,H3,"m")+1,,H2,32),0)
Dynamic array formulas.

 

[mvbernot]

I was thinking the datedif function might be used, but couldn't get the results I was looking for. Let me give this a go!!! I will get back to you!! Thank you for your time!!

 

[mvbernot]

I see that you are using a incremental amount of 32 in the sequence function. I guess over a longer period of time this might generate a list that might not include all the months Thank you very much! I appreciate the formula and will be using it in my workbook!!

 

[mvbernot]

For example 1/1/2020 to 9/30/2021 skips the September month.

MVB

 

[Fluff]

In that case we can go back to a modified version of the original
=EOMONTH(DATE(YEAR(H2),SEQUENCE(DATEDIF(H2,H3,"m")+1,,MONTH(H2)),1),0)

 

[mvbernot]

I changed the incremental number from 32 to 31 and it worked over that time period. That was my struggle, how to find the proper increment to use in "sequence".
Let me try the original too.

MVB

 

[Fluff]

As long as you are only going to run it over 2 years a step of 31 will work, it fails in year 5.