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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

T
  • Question Question
Formula to sum multiple sequences/ranges of values within an array?
Replies
2
Views
247
Fluff
Fluff
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
742
Fluff
Fluff
R
  • Solved
Excel/Google Sheets COUNTIFS formula to count rows, but with a substring in the Range.
2
Replies
11
Views
574
Fluff
Fluff
SaraWitch
  • Solved
Calculate number of days between two dates when one is created by another formula
Replies
2
Views
614
SaraWitch
SaraWitch
I
  • Solved
Indirect Formula help
Replies
7
Views
392
ItalianPlatinum
I

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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