Rooming list

E

Estela

New Member
Joined
Sep 13, 2017
Messages
2
Hi! Please I need your help. I have created a rooming list for an event with say 120 guest. I have their check in and check out dates. I need to be able to calculate how many rooms I need for a particular night.

The guests are booked between 23 Jan until 28 Jan. Their exact dates varies when they are participating.

For example, in excel I have the guest name with check in and out dates (3 columns)

Guest Check in Date. Check out date
1. 24 Jan 2017 26 Jan 2017
2. 25 Jan 2017 28 Jan 2017

And so on..

I want to be able to calculate how many do I need on the night of 24, on the night on 25, until 27th Jan.

Appreciate the help.

Thanks,
Estela
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Welcome to the board.

Try this:

ABCDEFGHIJK
GuestCheck In DateCheck Out Date
Al
Beatriz
Cal
Diego
Elayne
Frank
Giselle
Hank

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]23-Jan-2017[/TD]
[TD="align: right"]24-Jan-2017[/TD]
[TD="align: right"]25-Jan-2017[/TD]
[TD="align: right"]26-Jan-2017[/TD]
[TD="align: right"]27-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]24-Jan-2017[/TD]
[TD="align: right"]26-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]25-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]23-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]27-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]24-Jan-2017[/TD]
[TD="align: right"]25-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]25-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]26-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]24-Jan-2017[/TD]
[TD="align: right"]27-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet16​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=COUNTIFS($B:$B,"<="&F1,$C:$C,">"&F1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Put the dates in the top row. Put the formula in F2, then drag right. Hope this helps.
 
Upvote 0
Eric W said:
Welcome to the board.

Try this:

ABCDEFGHIJK
GuestCheck In DateCheck Out Date
Al
Beatriz
Cal
Diego
Elayne
Frank
Giselle
Hank

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]23-Jan-2017[/TD]
[TD="align: right"]24-Jan-2017[/TD]
[TD="align: right"]25-Jan-2017[/TD]
[TD="align: right"]26-Jan-2017[/TD]
[TD="align: right"]27-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]24-Jan-2017[/TD]
[TD="align: right"]26-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]25-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]23-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]27-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]24-Jan-2017[/TD]
[TD="align: right"]25-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]25-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]26-Jan-2017[/TD]
[TD="align: right"]28-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]24-Jan-2017[/TD]
[TD="align: right"]27-Jan-2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet16​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=COUNTIFS($B:$B,"<="&F1,$C:$C,">"&F1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Put the dates in the top row. Put the formula in F2, then drag right. Hope this helps.
Click to expand...


Hi Eric,

I seem to get an error when I try it on my sheet.

[TABLE="width: 255"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Check in [/TD]
[TD]Check out[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]07-Dec[/TD]
[TD="align: right"]10-Dec[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]07-Dec[/TD]
[TD="align: right"]10-Dec[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]07-Dec[/TD]
[TD="align: right"]10-Dec[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]07-Dec[/TD]
[TD="align: right"]10-Dec[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calculate monthly booked percentage of hotel rooms
2 3
Replies
21
Views
340
arendberg
A
A
  • Question Question
SUMIFS
Replies
1
Views
315
Rijnsent
Rijnsent
M
  • Question Question
Help Extracting first and second date from cell and date validation
Replies
0
Views
410
maximillianrg
M
B
  • Question Question
Copying data from a year tab and pasting into month tabs
Replies
2
Views
566
B-radK
B
G
  • Solved
Excel functions to determine hours worked on day or night shift
Replies
7
Views
524
Kaper
Kaper

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top