Calculate monthly booked percentage of hotel rooms

[arendberg]

Hey all,

I have a very big Excel file with all our hotel reservations in it. Column A has arrival dates, Column B has departure dates and Column C has the Room name. Every row shows 1 reservation.
In another sheet I would like to show per month the percentage of nights that have been reserved. So I input the Room name in this new sheet in N1 and for every month I will get a percentage showing how many nights have been booked in that month.

So if you take Room 1 for January 2025 you can see that it has been booked for 7 nights. So sheet 2 should show me a percentage of 21.6%. January has 31 possible nights that can be booked, 7 / 31 * 100.

I have tried a lot of things but so far have not been able to get the correct formula.

Hopefully someone here is able to help, thanks in advance!!

Example.xlsx
	A	B	C
1	Arrival	Departure	Room
2	5/1/24	5/3/24	Room 2
3	5/1/24	5/4/24	Room 1
4	5/9/24	5/20/24	Room 2
5	5/12/24	5/17/24	Room 1
6	5/27/24	5/29/24	Room 2
7	6/4/24	6/7/24	Room 2
8	6/6/24	6/9/24	Room 1
9	6/7/24	6/11/24	Room 2
10	6/12/24	6/16/24	Room 2
11	6/14/24	6/18/24	Room 1
12	6/20/24	6/23/24	Room 2
13	6/21/24	6/23/24	Room 1
14	6/23/24	6/25/24	Room 1
15	6/23/24	6/25/24	Room 2
16	6/25/24	6/30/24	Room 1
17	6/29/24	8/27/24	Room 2
18	7/5/24	7/9/24	Room 1
19	7/14/24	7/19/24	Room 1
20	7/23/24	7/26/24	Room 1
21	7/29/24	8/4/24	Room 1
22	8/9/24	8/12/24	Room 1
23	8/12/24	8/15/24	Room 1
24	8/16/24	8/18/24	Room 1
25	8/18/24	8/22/24	Room 1
26	8/25/24	8/29/24	Room 1
27	8/29/24	8/31/24	Room 2
28	8/29/24	9/1/24	Room 1
29	8/31/24	9/3/24	Room 2
30	9/3/24	9/7/24	Room 2
31	9/4/24	9/6/24	Room 1
32	9/8/24	9/11/24	Room 2
33	9/16/24	9/20/24	Room 2
34	9/17/24	9/22/24	Room 1
35	10/2/24	10/4/24	Room 1
36	10/2/24	10/5/24	Room 2
37	10/6/24	10/7/24	Room 1
38	10/6/24	10/10/24	Room 2
39	10/13/24	10/17/24	Room 2
40	10/21/24	10/25/24	Room 2
41	10/21/24	10/27/24	Room 1
42	10/29/24	11/10/24	Room 1
43	10/30/24	11/1/24	Room 2
44	11/1/24	11/3/24	Room 2
45	11/4/24	11/8/24	Room 2
46	11/8/24	11/10/24	Room 2
47	11/15/24	11/17/24	Room 1
48	11/20/24	11/24/24	Room 1
49	11/22/24	11/24/24	Room 2
50	11/28/24	11/29/24	Room 1
51	12/5/24	12/7/24	Room 2
52	12/7/24	12/9/24	Room 1
53	12/11/24	12/14/24	Room 1
54	12/14/24	12/16/24	Room 2
55	12/19/24	12/22/24	Room 2
56	12/20/24	12/22/24	Room 1
57	12/23/24	12/27/24	Room 2
58	12/23/24	12/27/24	Room 1
59	12/27/24	12/30/24	Room 1
60	12/27/24	12/30/24	Room 2
61	12/30/24	1/4/25	Room 2
62	12/31/24	1/4/25	Room 1
63	1/17/25	1/19/25	Room 1
64	1/18/25	1/25/25	Room 2
65	1/19/25	1/24/25	Room 1
66	1/25/25	2/1/25	Room 2
67	2/14/25	2/21/25	Room 2
68	2/20/25	2/25/25	Room 1
69	2/21/25	3/1/25	Room 2
70	3/10/25	3/15/25	Room 1
71	3/17/25	3/20/25	Room 1
72	3/17/25	3/23/25	Room 2
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
2:72	Expression	=#REF!<>""	text	YES

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Apr-25		Room 1
2									Formula that shows 22,6%
Sheet2

 

[Cubist]

Try adding this piece.

=LET(a,$A$2:$A$76,b,$B$2:$B$76,em,EOMONTH(F2,0),e,IF(b>em,em+1,b)-IF(a<F2,F2,a),SUM(($C$2:$C$76=$S$1)*($D$2:$D$76="")*($E$2:$E$76<=$P$1)*IF(e<0,0,e))/DAY(em))

 

[arendberg]

Perfect!!!