Calculate Occupancy using Arrival and Last Night

B

bastacks

New Member
Joined
Jul 12, 2017
Messages
3
I'm having trouble when calculating the nightly hotel occupancy using the arrival date and last night stayed.

Column A: Calendar dates (12/31/16. 1/1/17, etc)
Column B: Blank, no data
Column C: Arrival
Column D: Last night

The formula I was given is below, but the results are always 0 (zero). What am I doing wrong?

=COUNTIF($C$3:$C$9999,"<="&$A3)-COUNTIF($D$3:$D$9999,"<="&$A3)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Welcome to the Forum!

Is this what you're looking for?

B14: =COUNTIFS(C$2:C$11,"<="&A14,D$2:D$11,">="&A14)


Excel 2010
ABCD
1ArriveLast night
210 Jul 201712 Jul 2017
310 Jul 201713 Jul 2017
411 Jul 201711 Jul 2017
511 Jul 201712 Jul 2017
611 Jul 201715 Jul 2017
712 Jul 201712 Jul 2017
812 Jul 201713 Jul 2017
912 Jul 201715 Jul 2017
1013 Jul 201713 Jul 2017
1113 Jul 201716 Jul 2017
12
13Date to checkOccupancy
1412 Jul 20177
1513 Jul 20176
1614 Jul 20173
Sheet1
 
Upvote 0
Thanks, but I'm still getting a "0" result.
Formula in B3=
=COUNTIFS(C$2:C$50,"<="&A3,D$2:D$50,">="&A3)


[TABLE="class: grid, width: 348"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD]Arrivals[/TD]
[TD]Last Night[/TD]
[TD]Departure[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]0[/TD]
[TD]11/30/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/4/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/9/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/12/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/13/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/15/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/17/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/22/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/19/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/25/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/20/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/26/2016[/TD]
[TD]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/26/2016[/TD]
[TD]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/26/2016[/TD]
[TD]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/23/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/28/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/24/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/28/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/25/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/28/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/29/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/27/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/29/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/28/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/29/2016[/TD]
[TD]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/29/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/29/2016[/TD]
[TD]1/31/2017[/TD]
[TD="align: right"]2/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/30/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/30/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/30/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I'm getting a 0 result

B2: =COUNTIFS(C$2:C$50,"<="&A2,D$2:D$50,">="&A2)

[TABLE="width: 348"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Occupied Units[/TD]
[TD]Arrivals[/TD]
[TD]Last Night[/TD]
[TD]Departure[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]0[/TD]
[TD]11/30/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/4/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/9/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/11/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/12/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/13/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/15/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/17/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/1/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/18/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/22/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/19/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/25/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/20/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/26/2016[/TD]
[TD]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/26/2016[/TD]
[TD]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/22/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/26/2016[/TD]
[TD]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/23/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/28/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/24/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/28/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/25/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/28/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/26/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/29/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/27/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/29/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/28/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/29/2016[/TD]
[TD]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/29/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/29/2016[/TD]
[TD]1/31/2017[/TD]
[TD="align: right"]2/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/30/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/30/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/30/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/30/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]2/2/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/30/2016[/TD]
[TD]12/31/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[/TR]
[TR]
[TD="align: right"]2/3/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/30/2016[/TD]
[TD]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]2/4/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/30/2016[/TD]
[TD]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[/TR]
[TR]
[TD="align: right"]2/5/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/31/2016[/TD]
[TD]1/2/2017[/TD]
[TD="align: right"]1/3/2017[/TD]
[/TR]
[TR]
[TD="align: right"]2/6/2017[/TD]
[TD="align: right"]0[/TD]
[TD]12/31/2016[/TD]
[TD]1/3/2017[/TD]
[TD="align: right"]1/4/2017[/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
T
  • Question Question
Formula to sum multiple sequences/ranges of values within an array?
Replies
2
Views
311
Fluff
Fluff
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
761
Fluff
Fluff
A
  • Question Question
Find tool is very slow
Replies
2
Views
42
alex_345
A
seanjon
  • Question Question
Formula to subtract attendence points after 30 days, but only to one instance at a time.
Replies
4
Views
127
seanjon
seanjon

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