In Google Sheets I am trying to compute room occupancy per month from a list of dates-CodePudding

I have found numerous ways of getting parts of the result, but I'm obviously lacking in-depth knowledge of the various functions to arrive at what I need.
Sample data:

arrival | departure
11/25/2022 11/28/2022
11/30/2022 12/10/2022
12/25/2022 01/02/2023

I have 12 cells per year, one for each month. Each of those cells should contain a formula that shows the occupancy in that given month, derived and summed from the list of dates.
Expected result:
The cell for NOV 2022 should show 4 (3 nights between 11/25/2022 and 11/28/2022 1 night between 11/30/2022 and 12/10/2022)
The cell for DEC 2022 should show 16 (9 nights between 11/30/2022 and 12/10/2022 7 nights between 12/25/2022 and 1/02/2023)
The cell for JAN 2023 should show 1 (1 night between 12/25/2022 and 1/02/2023)

CodePudding user response：

try:

=ARRAYFORMULA(TRANSPOSE(QUERY(TEXT(QUERY(FLATTEN(IF(DAYS(B2:B5, A2:A5)>
 SEQUENCE(1, MAX(DAYS(B2:B5, A2:A5)), ), A2:A5 
 SEQUENCE(1, MAX(DAYS(B2:B5, A2:A5)), ), )), 
 "where Col1 is not null", ), {"e - mm", "1"}), 
 "select count(Col2) group by Col2 pivot Col1")))

CodePudding user response：

Try

=max(0,min($B2-1,EOMONTH(D$1,0))-max($A2,D$1)   1)

and drag ➪ ⇩