List all months that are part of a date range?

[Meraki22]

Hi

I'm having a problem with getting excel to list all the months and year that are part of a date range between a start and finish date. Below is the example of how I want it to behave (including partial months in it's output).

Desired output

Start	Finish	Output (This Spills out horizontally which is fine) ""indicates the result spills into seperate cells
10/01/2025	01/03/2025	"Jan-25" "Feb-25" "Mar-25"
31/03/2025	01/04/2025	"Mar-25" "Apr-25"
31/03/2025	01/05/2025	"Mar-25" "Apr-25" "May-25"

I've tried a few formulars to get this and it works apart from it only returns the month and year if it's at least a full month before the start date

Output I'm getting now

Start	Finish	Output
10/01/2025	01/03/2025	"Jan-25" "Feb-25"
31/03/2025	01/04/2025	"Mar-25"
31/03/2025	01/05/2025	"Mar-25" "Apr-25"

These are the formulars i've tried

=IF($C91="", "", IF(SEQUENCE(1, DATEDIF($C91, $E91, "m")+1, 0, 1)<=DATEDIF($C91, $E91, "m")+1, TEXT(EDATE($C91, SEQUENCE(1, DATEDIF($C91, $E91, "m")+1, 0, 1)), "MMM-YY"), IF(AND(MONTH($E91)<>MONTH($C91), YEAR($E91)=YEAR($C91)), TEXT($E91, "MMM-YY"), "")))

=IF($C91="", "", TEXT(EDATE($C91, SEQUENCE(1, DATEDIF($C91, $E91, "m")+1, 0, 1)), "MMM-YY"))

I think the issue is the "m")+1 part but i can't for the life of me workout how to fix it (Lots of googling and co-piloting) but i'm just not getting anywhere. any advice would be appreciated.

 

[hagia_sofia]

Hello, maybe something like:

=LET(
s,A2,
e,B2,
TEXT(DATE(YEAR(s),SEQUENCE(,MONTH(e)-MONTH(s)+1+(YEAR(e)-YEAR(s))*12,MONTH(s)),1),"MMM-YY"))

 

[Scott R]

This returns dates as values:

=LET( bmo, EOMONTH(A1,-1)+1,     emo, EOMONTH(B1,0),     mths, ROUND((emo-bmo)/30,0),
EDATE(bmo,SEQUENCE(,mths,0))
)

 

[Dave Patton]

T202503a.xlsm
	A	B	C	D	E	F	G
1		Start	Finish
2		10-Jan-25	1-Mar-25		Jan-25	Feb-25	Mar-25
3		31-Mar-25	1-Apr-25		Mar-25	Apr-25
4		31-Mar-25	1-May-25		Mar-25	Apr-25	May-25
5
6
7		10-Jan-25	1-Mar-25		Jan-25	Feb-25	Mar-25
8		31-Mar-25	1-Apr-25		Mar-25	Apr-25
9		31-Mar-25	1-May-25		Mar-25	Apr-25	May-25
10
7c

Cell Formulas
Range	Formula
E2:G2,E4:G4,E3:F3	E2	=TEXT(EDATE(B2,SEQUENCE(1,DATEDIF(B2-DAY(B2),C2,"m")+1,0,1)),"MMM-YY")
E7:G7,E9:G9,E8:F8	E7	=LET(s,B7-DAY(B7)+1,EDATE(s,SEQUENCE(,DATEDIF(s,C7,"m")+1)-1))
Dynamic array formulas.

 

[Meraki22]

Thank you everyone all of the above seem to work perfectly! Thank you!

 

[Meraki22]

T202503a.xlsm
	A	B	C	D	E	F	G
1		Start	Finish
2		10-Jan-25	1-Mar-25		Jan-25	Feb-25	Mar-25
3		31-Mar-25	1-Apr-25		Mar-25	Apr-25
4		31-Mar-25	1-May-25		Mar-25	Apr-25	May-25
5
6
7		10-Jan-25	1-Mar-25		Jan-25	Feb-25	Mar-25
8		31-Mar-25	1-Apr-25		Mar-25	Apr-25
9		31-Mar-25	1-May-25		Mar-25	Apr-25	May-25
10
7c

Cell Formulas
Range	Formula
E2:G2,E4:G4,E3:F3	E2	=TEXT(EDATE(B2,SEQUENCE(1,DATEDIF(B2-DAY(B2),C2,"m")+1,0,1)),"MMM-YY")
E7:G7,E9:G9,E8:F8	E7	=LET(s,B7-DAY(B7)+1,EDATE(s,SEQUENCE(,DATEDIF(s,C7,"m")+1)-1))
Dynamic array formulas.

The example in E2 doesn't seem to work quite right I set Start as 01/01/2025 and finish as 31/03/2025 and it gave Jan - Mar correctly but also Apr. The example in E7 seems to work perfectly though! thank you

 

[Dave Patton]

T202503a.xlsm
	A	B	C	D	E	F	G
1		Start	Finish
2		10-Jan-25	1-Mar-25		Jan-25	Feb-25	Mar-25
3		31-Mar-25	1-Apr-25		Mar-25	Apr-25
4		31-Mar-25	1-May-25		Mar-25	Apr-25	May-25
5		1-Jan-25	31-Mar-25		Jan-25	Feb-25	Mar-25
6
7		10-Jan-25	1-Mar-25		Jan-25	Feb-25
8		31-Mar-25	1-Apr-25		Mar-25	Apr-25
9		31-Mar-25	1-May-25		Mar-25	Apr-25	May-25
10		1-Jan-25	31-Mar-25		Jan-25	Feb-25	Mar-25
11
7c

Cell Formulas
Range	Formula
E2:G2,E4:G5,E3:F3	E2	=TEXT(EDATE(B2,SEQUENCE(1,DATEDIF(B2-DAY(B2)+1,C2,"m")+1,0,1)),"MMM-YY")
E7:F7	E7	=EDATE(B2,SEQUENCE(,DATEDIF(B7,C7,"m")+1)-1)
E8:F8,E9:G10	E8	=LET(s,B8-DAY(B8)+1,EDATE(s,SEQUENCE(,DATEDIF(s,C8,"m")+1)-1))
Dynamic array formulas.

 

[Dave Patton]

T202503a.xlsm
	A	B	C	D	E	F	G	H	I	J
1								List of months
2		Start	Finish		---------	-- Text --	---------	Custom Format	"mmm-yy"	---------
3		10-Jan-25	1-Mar-25		Jan-25	Feb-25	Mar-25	Jan-25	Feb-25	Mar-25
4		31-Mar-25	1-Apr-25		Mar-25	Apr-25		Mar-25	Apr-25
5		31-Mar-25	1-May-25		Mar-25	Apr-25	May-25	Mar-25	Apr-25	May-25
6		1-Jan-25	31-Mar-25		Jan-25	Feb-25	Mar-25	Jan-25	Feb-25	Mar-25
7c

Cell Formulas
Range	Formula
E3:G3,E5:G6,E4:F4	E3	=TEXT(EDATE(B3-DAY(B3)+1,SEQUENCE(,MONTH(C3)-MONTH(B3)+1)-1),"mmm-yy")
H3:J3,H5:J6,H4:I4	H3	=EDATE(B3-DAY(B3)+1,SEQUENCE(,MONTH(C3)-MONTH(B3)+1)-1)
Dynamic array formulas.