-1

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)

Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
Hans De Schryver
  • 43
  • 5

1 Answers1

2

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")))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124