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

 

[arendberg]

Ah I understand what you mean but we always look at nights not days. So the last night in any month should always be counted. So in this case from 28-2 to 01-03 is also a valid night for february.

Does that make sense?

 

[Cubist]

Hm.. try:

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

 

[arendberg]

Ok so now that February month is correct but another month has this row:

01/11/2024

21/12/2024

So it should also show as 100% but it is showing 96,7%?

Sorry.... But it seems you are almost there!

 

[Cubist]

I think this should do it. Adjust the range.

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

 

[arendberg]

Yes that fixed november but now december is incorrect, so sorry!

These are the lines with december 2024 reservations.

01/11/2024 21/12/2024
21/12/2024 28/12/2024
28/12/2024 04/01/2025

It looks like your formula doesn't take into account the december nights that are part of the reservation that started in november? I now have 35.5% for december while it should be 100%.
And the same goes for january 2025, there are 3 nights in the last december booking that are not calculated in January.

 

[Cubist]

I have a better idea of what you want but I have to think how to execute it in version 2021.

 

[Cubist]

Try this:

=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="")*IF(e<0,0,e))/DAY(em))

 

[arendberg]

Thank you for all your help, really appreciate it! So far I haven't found any problems with your latest solution.

 

[arendberg]

I hope you can help me with one final solution that would make this formula even better for our setup.

We have another column, E, with the date a reservation has been created. I would like to add a function to the formula that takes my input date and only looks at reservations made before that input date.
So in this example the date that is given in Sheet 2 P1 should make the formula take only the nights that have a creation date in Sheet 1 Column E before 01/12/2024.

Example.xlsx
	A	B	C	D	E
1	Arrival	Departure	Room	Cancelled On	Created On
2	2/1/25	2/8/25	Room 1		1/1/24
3	2/8/25	2/15/25	Room 1		1/2/24
4	2/15/25	2/22/25	Room 1		1/3/24
5	2/22/25	3/1/25	Room 1		1/4/24
6	5/27/24	5/29/24	Room 2		1/5/24
7	6/4/24	6/7/24	Room 2		1/6/24
8	6/6/24	6/9/24	Room 1		1/7/24
9	6/7/24	6/11/24	Room 2		1/8/24
10	6/12/24	6/16/24	Room 2	4/10/24	1/9/24
11	6/14/24	6/18/24	Room 1		1/10/24
12	6/20/24	6/23/24	Room 2		1/11/24
13	6/21/24	6/23/24	Room 1		1/12/24
14	6/23/24	6/25/24	Room 1		1/13/24
15	6/23/24	6/25/24	Room 2		1/14/24
16	6/25/24	6/30/24	Room 1		1/15/24
17	6/29/24	8/27/24	Room 2		1/16/24
18	7/5/24	7/9/24	Room 1		1/17/24
19	7/14/24	7/19/24	Room 1		1/18/24
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
20:72,C2:XFD2,C3:D5,A6:D19,E3:XFD19	Expression	=#REF!<>""	text	YES

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
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		12/1/24
2	0.0%	53.3%	45.2%	58.1%	23.3%	35.5%	23.3%	45.2%	22.6%	114.3%	25.8%	0.0%
Sheet2

Cell Formulas
Range	Formula
A2:L2	A2	=LET(arrival,Sheet1!$A$2:$A$72,depart,Sheet1!$B$2:$B$72,room,A1,SUM(FILTER(IF(depart>EOMONTH(+arrival,0),EOMONTH(+arrival,0),depart)-arrival,(Sheet1!$C$2:$C$72=$N$1)*(EOMONTH(+arrival,-1)+1=room),0))/DAY(EOMONTH(room,0)))

 

[arendberg]

Don't mind the wrong formula in this example, I haven't updated it with your last version. Oh and I use the latest MacOS version of Excel, so not 2021 but 365. Not sure if this helps you in any way?