Hotel Booking Rooms Count

[babar1988]

I am using an excel file to count the hotel reserved room counts by entering the reservation details, for example:

if guests reserved 25th till 30th 2 double, it should the total rooms consumed from 25th till 30th, each day 2 units

if another guest booked from 25th till 27th 5 double rooms, then it means the total from 25th till 27th, the room reserved 7 and from 28 till 30th 2 rooms.

Room Chart-updatedrooms.xlsx
	A	B	C	D	E	F	G	H	I
1	S.No.	Group Name	Check-in	Check-out	Nights	Double	Triple	Quad	Total
2	1	Babar 1	25-Mar	29-Mar	4	2	3	4	9
3	2	Babar 2	25-Mar	30-Mar	5	2	3	4	9
4	3	Babar 3	25-Mar	29-Mar	4	2	3	4	9
Data Sheet

Cell Formulas
Range	Formula
E2:E4	E2	=D2-C2
I2:I4	I2	=H2+F2+G2

Room Chart-updatedrooms.xlsx
	A	B	C	D
1	Date	Double	Triple	Quad
2	25-Mar	2	0
3	26-Mar
4	27-Mar
5	28-Mar
6	29-Mar
7	30-Mar
8	31-Mar
9	1-Apr
10	2-Apr
11	3-Apr
12	4-Apr
13	5-Apr
14	6-Apr
15	7-Apr
16	8-Apr
Availablity Sheet

Cell Formulas
Range	Formula
B2	B2	=IF(AND(A2>='Data Sheet'!C2,A$2<='Data Sheet'!D2-1),'Data Sheet'!F2,0)
C2	C2	=IF(AND(A2>='Data Sheet'!D2,A$2<='Data Sheet'!E2-1),'Data Sheet'!G2,0)+IF(AND(B2>='Data Sheet'!D2,B$2<='Data Sheet'!E2-1),'Data Sheet'!G2,0)

if was using this formula, but found it not useful by applying all the cells and using two many columns.

=IF(AND(A2>='Data Sheet'!C2,A$2<='Data Sheet'!D2-1),'Data Sheet'!F2,0)

is there any way using VBA or formula to solve this issue?

 

[gravanoc]

I'm not understanding this question very well. Take the first row, for example, what is illustrated here? To me, it looks like a group named Babar 1 spent 4 nights during which they rented 2 double bedrooms, 3 triple rooms, & 4 quad rooms. Is that what's happening? Next, on the Availability Sheet, it seems like the result you're aiming for would just be a replication of the #'s associated with the first data set's room assignments. If you could, please elaborate on this. Perhaps fill in what the end result should be, and then we can work from there.

 

[jtakw]

Hi,

Do you mean something like this:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	S.No.	Group Name	Check-in	Check-out	Nights	Double	Triple	Quad	Total
2	1	Babar 1	3/25/2022	3/29/2022	4	2	3	4	9
3	2	Babar 2	3/25/2022	3/30/2022	5	2	3	4	9
4	3	Babar 3	3/25/2022	3/29/2022	4	2	3	4	9
Data Sheet

Book3.xlsx
	A	B	C	D
1	Date	Double	Triple	Quad
2	3/25/2022	6	9	12
3	3/26/2022	6	9	12
4	3/27/2022	6	9	12
5	3/28/2022	6	9	12
6	3/29/2022	6	9	12
7	3/30/2022	2	3	4
8	3/31/2022	0	0	0
9	4/1/2022	0	0	0
10	4/2/2022	0	0	0
11	4/3/2022	0	0	0
12	4/4/2022	0	0	0
13	4/5/2022	0	0	0
14	4/6/2022	0	0	0
15	4/7/2022	0	0	0
16	4/8/2022	0	0	0
Sheet1049

Cell Formulas
Range	Formula
B2:D16	B2	=SUMPRODUCT(('Data Sheet'!$C$2:$C$4<=$A2)*('Data Sheet'!$D$2:$D$4>=$A2)*('Data Sheet'!$F$1:$H$1=B$1)*'Data Sheet'!$F$2:$H$4)

 

[babar1988]

Hi,

Do you mean something like this:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	S.No.	Group Name	Check-in	Check-out	Nights	Double	Triple	Quad	Total
2	1	Babar 1	3/25/2022	3/29/2022	4	2	3	4	9
3	2	Babar 2	3/25/2022	3/30/2022	5	2	3	4	9
4	3	Babar 3	3/25/2022	3/29/2022	4	2	3	4	9
Data Sheet

Book3.xlsx
	A	B	C	D
1	Date	Double	Triple	Quad
2	3/25/2022	6	9	12
3	3/26/2022	6	9	12
4	3/27/2022	6	9	12
5	3/28/2022	6	9	12
6	3/29/2022	6	9	12
7	3/30/2022	2	3	4
8	3/31/2022	0	0	0
9	4/1/2022	0	0	0
10	4/2/2022	0	0	0
11	4/3/2022	0	0	0
12	4/4/2022	0	0	0
13	4/5/2022	0	0	0
14	4/6/2022	0	0	0
15	4/7/2022	0	0	0
16	4/8/2022	0	0	0
Sheet1049

Cell Formulas
Range	Formula
B2:D16	B2	=SUMPRODUCT(('Data Sheet'!$C$2:$C$4<=$A2)*('Data Sheet'!$D$2:$D$4>=$A2)*('Data Sheet'!$F$1:$H$1=B$1)*'Data Sheet'!$F$2:$H$4)

Hi @jtakw,

Thank you very much, yes i am looking for the same thing, please tell me how you did this.

 

[jtakw]

You're welcome.

If you want to see how the SUMPRODUCT is working here, you can Evaluate the formula, follow these steps:

• Select the cell that you want to evaluate. ...
• On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.
• Click Evaluate to examine the value of the underlined reference. ...
• Continue until each part of the formula has been evaluated.
• To see the evaluation again, click Restart.

 

[babar1988]

I'm not understanding this question very well. Take the first row, for example, what is illustrated here? To me, it looks like a group named Babar 1 spent 4 nights during which they rented 2 double bedrooms, 3 triple rooms, & 4 quad rooms. Is that what's happening? Next, on the Availability Sheet, it seems like the result you're aiming for would just be a replication of the #'s associated with the first data set's room assignments. If you could, please elaborate on this. Perhaps fill in what the end result should be, and then we can work from there.

Hi @gravanoc,

Thank you very much, This is the final results i am looking for.

Room Chart-updatedrooms.xlsx
	A	B	C	D
1	Date	Double	Triple	Quad
2	25-Mar	6	9	12
3	26-Mar	6	9	12
4	27-Mar	6	9	12
5	28-Mar	6	9	12
6	29-Mar	6	9	12
7	30-Mar	2	3	4
Availablity Sheet

Room Chart-updatedrooms.xlsx
	A	B	C	D	E	F	G	H	I
1	S.No.	Group Name	Check-in	Check-out	Nights	Double	Triple	Quad	Total
2	1	Babar 1	25-Mar	29-Mar	4	2	3	4	9
3	2	Babar 2	25-Mar	30-Mar	5	2	3	4	9
4	3	Babar 3	25-Mar	29-Mar	4	2	3	4	9
Data Sheet

Cell Formulas
Range	Formula
E2:E4	E2	=D2-C2
I2:I4	I2	=H2+F2+G2

 

[jtakw]

I'm puzzled by your Post # 6.

I Already gave you the formula to achieve the results.

 

[babar1988]

I'm puzzled by your Post # 6.

I Already gave you the formula to achieve the results.

hi @jtakw,


Thank you very much, all is working totally fine, I replied to him before saw your reply.

million thanks, bro.

 

[jtakw]

You're welcome.

But I think you're supposed to mark the Actual post providing the solution (i.e. Post # 3)
Not your own post.

 

[babar1988]

You're welcome.

But I think you're supposed to mark the Actual post providing the solution (i.e. Post # 3)
Not your own post.

Yes market post number #3

the last thing, if I don't want to count the checkout date, is there any solution for that?

mean check-in 25th checkout 30th for 2 double rooms

the count should show from 25th till 29th, please advise? and many thanks in advance

@jtakw