Row duplicate for each month.

[wikus]

Hi,
The source sheet have a start date and end date columns.
I want to add a third column which combines year and month and duplicate the rows from start date month to end date month as below on a different sheet. (The source sheet have many columns and rows).

Any help or advice will be appreciated.

Wikus

 

[duggie33]

Hi wikus,

I was able to get this far...

Book1
	A	B	C
1	Start Date	End Date	YearMonth
2	11/18/2022	5/12/2024	2022M11
3			2022M12
4			2023M1
5			2023M2
6			2023M3
7			2023M4
8			2023M5
9			2023M6
10			2023M7
11			2023M8
12			2023M9
13			2023M10
14			2023M11
15			2023M12
16			2024M1
17			2024M2
18			2024M3
19			2024M4
20			2024M5
Sheet3

Cell Formulas
Range	Formula
C2:C20	C2	=DATE(YEAR($A$2),MONTH($A$2)+SEQUENCE(DATEDIF(A2,DATE(YEAR(B2),MONTH(B2),DAY(A2)),"m")+1,,0,1),1)
Dynamic array formulas.

Somehow I remembered the DATEDIF function from years ago...the function that doesn't show up when typing it.

Anyway, I am not good at stacking these functions if there when there more dates below those in A2:B2. There are many in here who are able to add to what I have done to make that happen...calling the pros to step in please

Hope that helps,

Doug

 

[wikus]

Hi wikus,

I was able to get this far...

Book1
	A	B	C
1	Start Date	End Date	YearMonth
2	11/18/2022	5/12/2024	2022M11
3			2022M12
4			2023M1
5			2023M2
6			2023M3
7			2023M4
8			2023M5
9			2023M6
10			2023M7
11			2023M8
12			2023M9
13			2023M10
14			2023M11
15			2023M12
16			2024M1
17			2024M2
18			2024M3
19			2024M4
20			2024M5
Sheet3

Cell Formulas
Range	Formula
C2:C20	C2	=DATE(YEAR($A$2),MONTH($A$2)+SEQUENCE(DATEDIF(A2,DATE(YEAR(B2),MONTH(B2),DAY(A2)),"m")+1,,0,1),1)
Dynamic array formulas.

Somehow I remembered the DATEDIF function from years ago...the function that doesn't show up when typing it.

Anyway, I am not good at stacking these functions if there when there more dates below those in A2:B2. There are many in here who are able to add to what I have done to make that happen...calling the pros to step in please

Hope that helps,

Doug

Thanks Doug, this is a good start.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C
1	Start Date	End Date	YearMonth
2	12/09/2023	01/04/2024	2023M9
3	18/11/2022	12/05/2024	2023M10
4			2023M11
5			2023M12
6			2024M1
7			2024M2
8			2024M3
9			2024M4
10			2022M11
11			2022M12
12			2023M1
13			2023M2
14			2023M3
15			2023M4
16			2023M5
17			2023M6
18			2023M7
19			2023M8
20			2023M9
21			2023M10
22			2023M11
23			2023M12
24			2024M1
25			2024M2
26			2024M3
27			2024M4
28			2024M5
29			2024M6
30
Data

Cell Formulas
Range	Formula
C2:C29	C2	=LET(d,DATEDIF(EOMONTH(--A2:A3,-1),EOMONTH(--B2:B3,0),"m")+1,TOCOL(IF(SEQUENCE(,MAX(d))<=d,TEXT(DATE(YEAR(A2:A10),MONTH(A2:A10)+SEQUENCE(,MAX(d),0),1),"yyyy""M""m"),1/0),2))
Dynamic array formulas.

 

[wikus]

Another option

Fluff.xlsm
	A	B	C
1	Start Date	End Date	YearMonth
2	12/09/2023	01/04/2024	2023M9
3	18/11/2022	12/05/2024	2023M10
4			2023M11
5			2023M12
6			2024M1
7			2024M2
8			2024M3
9			2024M4
10			2022M11
11			2022M12
12			2023M1
13			2023M2
14			2023M3
15			2023M4
16			2023M5
17			2023M6
18			2023M7
19			2023M8
20			2023M9
21			2023M10
22			2023M11
23			2023M12
24			2024M1
25			2024M2
26			2024M3
27			2024M4
28			2024M5
29			2024M6
30
Data

Cell Formulas
Range	Formula
C2:C29	C2	=LET(d,DATEDIF(EOMONTH(--A2:A3,-1),EOMONTH(--B2:B3,0),"m")+1,TOCOL(IF(SEQUENCE(,MAX(d))<=d,TEXT(DATE(YEAR(A2:A10),MONTH(A2:A10)+SEQUENCE(,MAX(d),0),1),"yyyy""M""m"),1/0),2))
Dynamic array formulas.

Thank you, much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback.