# Count Activities in a Date Range

Today's challenge is to count how many guests stayed at a hotel, in a specific date range, based on the guest arrival and departure dates.

### Find Specific Dates

In previous examples, we've seen how to check if a specific date falls within a date range. For example, if you have a list of orders, you can use SUMIF or SUMIFS, to add up all the orders between a start and end date. You can see the written instructions for this on my website.

### Guest Visits

It is a little trickier to count hotel guests though, because the booking table only shows the guest arrival and departure dates. We have to use those dates, to see if any part of a guest's visit overlapped our date range.

For this report, we want the number of guest who were in the hotel during the reporting period of December 7th to 9th. I've highlighted the records that should be included, when we create a formula to count the guests.

### When Did the Guest Leave?

To calculate if the guest's visit was within the date range, we'll start with two short formulas, in helper columns.

The first formula will check the guest's departure date – was it on or after the start of our date range? (If they left before the date range started, they won't be counted)

The report start date is in cell C3, and guest's departure date is in cell C6, so the formula is:

**=C6>=$C$3**

The first 5 guests all left before December 7th, so the result in those rows is FALSE.

### When Did the Guest Arrive?

The second formula will check the guest's arrival date – was it on or before the end of our date range? (If they arrived after the date range ended, they won't be counted)

The report start date is in cell D3, and guest's arrival date is in cell B6, so the formula is:

**=B6<=$D$3**

Guest #11 arrived on December 10, and that is after the report end date of December 9th, so the result in that row is FALSE.

### Both Results TRUE

If the result of both formulas is TRUE, then the guest stayed at the hotel during the reporting period:

- they arrived on or before December 9th
- they departed on or after December 7th

We'll use a SUMIFS formula to check those two columns, and sum then number of guests in column E. I've changed the column headings in F and G, to **Dep Check** and **Arr Check**.

Enter this formula in cell F3:

**=SUMIFS(tblGuests[Guests], tblGuests[Dep Check],TRUE, tblGuests[Arr Check],TRUE)**

### Get the Result

The result is 11, and that matches the total when the numbers are selected in the manually highlighted rows. You can see that automatic calculation in the Status Bar.

Note: Instead of using the formulas in helper columns, you could combine the date checks with the formula in cell F3:

**=SUMIFS(tblGuests[Guests], tblGuests[Depart],">=" & $C$3,**

**tblGuests[Arrive],"<=" & $D$3)**

### Download the Sample File

To see how the formulas work, you can download the sample file from my Contextures website. On the Excel Sample Files page, go to the Functions section, and look for FN0036 - Count Hotel Guests in Date Range.

The zipped file is in xlsx format, and does not contain macros. It also has another, more complex, example of counting in a date range. Guests are counted by their hotel loyalty program level, and the number of nights booked in the date range is also calculated.

____________________________

Thanks David, and I’ll keep that in mind for another post.

Hi Debra,

Here’s my approach in calculating the number of nights.

=MAX(0,MIN([@Depart]-1,$C$3)-MAX([@Arrive],$B$3)+1)

You are more than welcome to visit my post for this formula.

http://wmfexcel.wordpress.com/2014/10/25/how-to-calculate-number-of-overlapping-days-for-two-periods/

Cheers,

Why do we need two helper columns when sumifs gives the same result?

I would go with simple formula like this:

With Regards

Rudra

@Runda, thanks, and yes, it could all be done in one SUMIFS formula — I used the helper columns to make it simpler to follow the steps.

I’ve added the longer formula to the article:

=SUMIFS(tblGuests[Guests],tblGuests[Depart],”>=” & $C$3,tblGuests[Arrive],”<=" & $D$3)

I need to put a formula wherein date should be in dropdown and when I choose a particular date, only that dates data should populate in excel sheet. Please help me this.

how can I get all the names of guests within a particular date range to a particular cell, either using concat or something similar?

something like “Names of all the guests stayed in the hotel on 1 Dec”?

If you’re using Excel 2016 (subscriber version), there is a TEXTJOIN function that can combine the data. Mike Girvin has several videos on that topic in this playlist on his ExcelIsFun YouTube channel:

https://www.youtube.com/playlist?list=PLrRPvpgDmw0lk4k5Qs_WmIdLAuViJEw8j