Counting the number of days in specific months from date range. Google sheets

Question

I am trying to make a google sheet where it will automatically calculate how many days a device was in repair per month.

I need it to calculate the dates in columns and to post the results in a different column rows since there will be a list which will constantly have new entries

Ex:

Device| start date | end date.

Laptop| 8/29/2021 | 9/10/2021. 2 days in August,  10 days in September 

The desired result would it be in 2 columns 1 for month 1, column 2 for month 2

I managed to find this formula:

=ArrayFormula(query({EOMONTH(row(indirect("A"&A2):indirect("A"&B2)),0),row(indirect("A"&A2):indirect("A"&B2))},"Select Col1,Count(Col2) group by Col1 label Col1 'Month', count(Col2)'Days in Month' format Col1 'MMMM-YYYY' "))

but I cannot use it in a list sheet. Any ideas would be appreciated

Answer 1

try:

=ARRAYFORMULA(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(IFERROR(SPLIT(FLATTEN(IF((A1:A="")+(B1:B=""),,
 IF(C1:C-B1:B>=SEQUENCE(1, MAX(C1:C-B1:B), ), 
 ROW(A1:A)&"×"&TEXT((B1:B)+SEQUENCE(1, MAX(C1:C-B1:B), ), "mm"), ))), "×")), 
 "select count(Col1) where Col2 is not null group by Col1 pivot Col2"), "offset 1", 0)),,9^9)), " "))

demo spreadsheet