CODEWITHSUNDEEP
exceldateexcel-formula
Anthony M
Anthony M

Reputation: 13

In excel, calculate the hotel group pickup pattern using check in and check out dates

I receive large lists of names and check-in and check-out dates for groups at a hotel. Often times, I receive errors in the hotel system if I do not have an accurate number of rooms to save a list into.

What I need is an Excel formula that can calculate consistently the total number of rooms I will need for a given night using only the check-in and check-out dates provided.

I've tried "COUNTIFS" before:

=COUNTIFS(arrival-range,"{greater than/equal to"&DATE(yyyy,m,d),departure-range,"{less than/equal to}"&DATE(yyyy,m,d)

but it doesn't accurately return the booking pattern of rooms booked per night.

Example:
run code snippet for example tables of data

td {font-family: sans-serif; padding: 2; text-align: center;}
#have {display:inline; margin: 5;}
#have td {border: 1px solid blue;}
#want {display:inline;}
#want td {border: 1px solid green;}
.rmval {font-weight: bold; background-color: #eafceb;}
<table id="have">
<tr><td colspan="2">Rooming List</td></tr>
<tr id="label">
<td id="arrival">Arrival</td><td id="depart">Departure</td>
</tr>
<tr><td>01-Month</td><td>04-Month</td></tr>
<tr><td>01-Month</td><td>03-Month</td></tr>
<tr><td>02-Month</td><td>04-Month</td></tr>
<tr><td>02-Month</td><td>03-Month</td></tr>
<tr><td>01-Month</td><td>04-Month</td></tr>
</table>

<table id="want">
<tr><td colspan="2">Data I Want</td></tr>
<tr><td>Date</td><td>Number of rooms</td></tr>
<tr><td>01 Month</td><td class="rmval">3</td></tr>
<tr><td>02 Month</td><td class="rmval">5</td></tr>
<tr><td>03 Month</td><td class="rmval">3</td></tr>
</table>

Thanks in advance for help

.

Upvotes: 1

Views: 702

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Searching for the number of bookings for a date present in C3, this formula should work unless something is missing in your question:

=COUNTIFS(A:A, "<="&C3, B:B, ">"&C3)

For today:

=COUNTIFS(A:A, "<="&TODAY(),B:B, ">"&TODAY())

Upvotes: 0

Related Questions