Allocate between dates to months then summarise by month and year

[Robert Davidson]

I am seeking help how to split bookings by date and summarising it by month and year. Thus an example of the data is:

Start

End

Jan-2024​

Feb-2024​

Mar-2024​

Apr-2024​

May-2024​

Jun-2024​

Jul-2024​

Aug-2024​

Sep-2024​

Oct-2024​

Nov-2024​

Dec-2024​

Jan-2025​

Feb-2025​

Mar-2025​

Apr-2025​

15/01/2024​

02/02/2024​

17​

1​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

26/02/2024​

04/03/2024​

0​

4​

3​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

05/04/2024​

06/04/2024​

0​

0​

0​

1​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

06/04/2024​

12/06/2024​

0​

0​

0​

25​

31​

11​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

30/12/2024​

05/01/2025​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

2​

4​

0​

0​

0​

05/09/2024​

12/10/2024​

0​

0​

0​

0​

0​

0​

0​

0​

26​

11​

0​

0​

0​

0​

0​

0​

14/06/2024​

16/06/2024​

0​

0​

0​

0​

0​

2​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

26/02/2025​

04/03/2025​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

3​

3​

0​

I can split the bookings by month using the following formula which is in the Jan 2024 column (second row): =MAX(0,(MIN($B2-1,DATE(YEAR(C$1),MONTH(C$1)+1,0))-MAX($A2,C$1)+1)).
However I would like to generate the following table only using the above Start and End columns and dispense with the remainder of the above columns:

Month	2024​	2025​
Jan​	17​	4​
Feb​	5​	3​
Mar​	3​	3​
Apr​	26​	0​
May​	31​	etc. the table would build into 2026, 2027 etc as more bookings are added
Jun​	13​
Jul​	0​
Aug​	0​
Sep​	26​
Oct​	11​
Nov​	0​
Dec​	2​

 

[Robert Davidson]

It would be great if someone knew how to do this. Any takers?

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Start	End	Jan-2024	Feb-2024	Mar-2024	Apr-2024	May-2024	Jun-2024	Jul-2024	Aug-2024	Sep-2024	Oct-2024	Nov-2024	Dec-2024	Jan-2025	Feb-2025	Mar-2025	Apr-2025
2	1/15/2024	2/2/2024	17	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	2/26/2024	3/4/2024	0	4	3	0	0	0	0	0	0	0	0	0	0	0	0	0
4	4/5/2024	4/6/2024	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
5	4/6/2024	6/12/2024	0	0	0	25	31	11	0	0	0	0	0	0	0	0	0	0
6	12/30/2024	1/5/2025	0	0	0	0	0	0	0	0	0	0	0	2	4	0	0	0
7	9/5/2024	10/12/2024	0	0	0	0	0	0	0	0	26	11	0	0	0	0	0	0
8	6/14/2024	6/16/2024	0	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0
9	2/26/2025	3/4/2025	0	0	0	0	0	0	0	0	0	0	0	0	0	3	3	0
10
11
12
13	Month	2024	2025
14	Jan	17	4
15	Feb	5	3
16	Mar	3	3
17	Apr	26	0
18	May	31	0
19	Jun	13	0
20	Jul	0	0
21	Aug	0	0
22	Sep	26	0
23	Oct	11	0
24	Nov	0	0
25	Dec	2	0
Sheet7

Cell Formulas
Range	Formula
C2:R9	C2	=MAX(0,(MIN($B2-1,EOMONTH(C$1,0))-MAX($A2,C$1)+1))
B14:C25	B14	=LET(s,$A$2:$A$9,e,$B$2:$B$9-1,ys,DATE(B13,SEQUENCE(,12),1),ye,EOMONTH(ys,0),m,SEQUENCE(,ROWS(s),,0),c,IF(e<ye,e,ye)-IF(s>ys,s,ys)+1,z,IF(c<0,0,c),TRANSPOSE(MMULT(m,z)))
Dynamic array formulas.

 

[Robert Davidson]

That's great Eric @Eric W ,

It works fine but would it be possible for the array to expand dynamically as more booking in future years are added? Can the months and years be added dynamically to the formula as row and column headings?

 

[Eric W]

The months don't need to be expanded, since there are only 12 months. The year headings can expand horizontally easily enough. However, the formula itself I don't see how to make it dynamically expand horizontally. Internally it's already a 2D function, and that would make it a 3D function. Standard Excel functions can only handle 2D. It might be possible using some of the newer functions, but I don't have those to work with. Even then, I have a hard time imagining it. Maybe with VBA, but you'd still need a way to initiate the macro.

I'd just put in year headings for the next 10 years, and put the formulas for each of the years under them. If you want, you can use Conditional Formatting to hide any columns that don't have any non-zero values. That way it would appear to dynamically update.

 

[Robert Davidson]

The months don't need to be expanded, since there are only 12 months. The year headings can expand horizontally easily enough. However, the formula itself I don't see how to make it dynamically expand horizontally. Internally it's already a 2D function, and that would make it a 3D function. Standard Excel functions can only handle 2D. It might be possible using some of the newer functions, but I don't have those to work with. Even then, I have a hard time imagining it. Maybe with VBA, but you'd still need a way to initiate the macro.

I'd just put in year headings for the next 10 years, and put the formulas for each of the years under them. If you want, you can use Conditional Formatting to hide any columns that don't have any non-zero values. That way it would appear to dynamically update.

What you have done is great so thank you very much. Robert

 

[Robert Davidson]

What you have done is great so thank you very much. Robert

Eric,

If I had another column which had the status of the booking e.g. Confirmed or Cancelled, how could I perform the calculation to exclude any Cancelled bookings?

 

[Eric W]

Hard to say, it sounds pretty challenging. Do you have a sample of what it would look like? Where that column would go?