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:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Arrival	Departure	Room															Room 1
2	5/1/24	5/3/24	Room 2		5/1/24	6/1/24	7/1/24	8/1/24	9/1/24	10/1/24	11/1/24	12/1/24	1/1/25	2/1/25	3/1/25	4/1/25
3	5/1/24	5/4/24	Room 1		25.8%	53.3%	58.1%	61.3%	23.3%	67.7%	23.3%	58.1%	22.6%	17.9%	25.8%	0.0%
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
Sheet8

Cell Formulas
Range	Formula
E3:P3	E3	=SUM(FILTER($B$2:$B$72-$A$2:$A$72,($C$2:$C$72=$R$1)*(EOMONTH(+$A$2:$A$72,-1)+1=E2),0))/DAY(EOMONTH(E2,0))

 

[arendberg]

You are amazing!! I think this is nearly it. Can you please check what is wrong with June 24 for Room 2? It shows 250%?
I think it has something to do with the fact that it has a reservation with arrival on 29-06 and departure on 27-08. The formula incorrectly is taking all those days while it should only calculate the days in June?

 

[Cubist]

How about this?

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Arrival	Departure	Room															Room 2
2	5/1/24	5/3/24	Room 2		5/1/24	6/1/24	7/1/24	8/1/24	9/1/24	10/1/24	11/1/24	12/1/24	1/1/25	2/1/25	3/1/25	4/1/25
3	5/1/24	5/4/24	Room 1		48.4%	56.7%	0.0%	6.5%	36.7%	51.6%	33.3%	48.4%	41.9%	50.0%	19.4%	0.0%
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
Sheet8

Cell Formulas
Range	Formula
E3:P3	E3	=LET( arrival,$A$2:$A$72, depart,$B$2:$B$72, room,E2, SUM(FILTER(IF(depart>EOMONTH(+arrival,0),EOMONTH(+arrival,0),depart)-arrival,($C$2:$C$72=$R$1)*(EOMONTH(+arrival,-1)+1=room),0))/DAY(EOMONTH(room,0)) )

 

[arendberg]

Ah yes I think now it is correct, thank you so much!

One last thing I just though of. We also put our cancellations in this sheet. These should not count of course. Can you adjust your formula that when there is a date present in Column D it doesn't count the nights in that row?

Example.xlsx
	A	B	C	D
1	Arrival	Departure	Room	Cancelled
2	5/1/24	5/3/24	Room 2	2/1/24
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	4/10/24
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	7/20/24
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

 

[Cubist]

Try:

=LET(
arrival,$A$2:$A$72,
depart,$B$2:$B$72,
SUM(FILTER(IF(depart>EOMONTH(+arrival,0),EOMONTH(+arrival,0),depart)-arrival,($C$2:$C$72=$S$1)*(EOMONTH(+arrival,-1)+1=F2)*($D$2:$D$72=""),0))/DAY(EOMONTH(F2,0))
)

 

[arendberg]

Amazing, it works perfectly.

Again thank you so much!

 

[Cubist]

You're welcome.

 

[arendberg]

Sorry, I was to quick

I have now put the formula in the big sheet with over 2000 rows of data and I see that not all of them calculate correctly. Especially months that are 100% are not showing as 100%?

Take this as an example, this room has 28 nights booked in February 2025 but your formula shows it as 96.4%?

Example.xlsx
	A	B	C	D
1	Arrival	Departure	Room	Cancelled
2	2/1/25	2/8/25	Room 1
3	2/8/25	2/15/25	Room 1
4	2/15/25	2/22/25	Room 1
5	2/22/25	3/1/25	Room 1
Sheet1

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

 

[Cubist]

Because row 5 is only 6 days i.e. 2/22/25 to 2/28/25. It's excluding 3/1/25 as you requested in post #3.