Need help with excel

Hi Anu2021,

I am offering you a solution through a formula. This formula works for Excel 365. I have applied this formula in cell A9.

I hope this helps.

=LET( data, A2:N6, dr, DROP(data, 1), tk, TAKE(dr,, 2), em, EOMONTH(TOCOL(tk), 0), min, MIN(em), unq, UNIQUE(EOMONTH(SEQUENCE(, MAX(em) - min + 1, min), 0), TRUE), VSTACK(HSTACK(TAKE(TAKE(data, 1),, 2), unq), HSTACK(tk, IFERROR(--TEXTSPLIT(TEXTJOIN(";", FALSE, BYROW(dr, LAMBDA(a, ARRAYTOTEXT(IFERROR( 1 / (1 / INDEX(IFERROR(HSTACK(SIGN(SEQUENCE(, XMATCH(TRUE, EOMONTH(TAKE(a,, 1), 0) = unq) -1)) - 1, DROP(a,, 2)), DROP(a,, 2)),, SEQUENCE(, COUNT(unq)))), ""))))), ", ", ";"), ""))) )

Hi

99% of people who come here Need help with excel. It would help people who search for existing solutions if you update you thread title with something that better describe the issue - Thanks

With Power Query:

Sample available at: https://1drv.ms/x/c/1cd824d35610aacb/IQB7VWEG6LWjR7hGfw9OAx3BAXmsvz6TbdPNoIZT5efUBV4?e=XDI1Gn

With your data in the blue/input table, right click in the green table > Refresh

Assuming you always want a count of 1 when the month column is encompassed by the start & end dates of the row (I mention this as in the image example some fields have 2 or 3 and I don’t understand why)…

The fields for start date, end date and the headers for the month columns will all need to be formatted as date cells.
The rest of the table will need to be formatted as number cells. 

This approach takes the header month date and asks if the ‘period begin start of month’ is before the header month ended AND if the ‘period end end of month’ in is after the header month began.

The formula:

=if(AND(EOMONTH($A2,-1)+1<=EOMONTH(C$2,0),EOMONTH($B2,0)>=EOMONTH(C$2,-1)+1),1,0)