Cellwise Start free

Excel guide · 4 min read

How to calculate occupancy rate in Excel

Quick answerOccupancy rate = booked nights / available nights. Count each booking's nights inside the month with =MAX(0,MIN(D2,$I$3+1)-MAX(C2,$I$2)), add them up, and divide by the nights you could have rented (days in the month x properties, minus blocked nights). Here that's 35 / 59 = 59.3% for October.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGHI
1PropertyGuestCheck-inCheck-outBooking totalNights in monthSettingValue
2Lake CabinPatelSep 27, 2026Oct 3, 20266902Month startOct 1, 2026
3Lake CabinNguyenOct 9, 2026Oct 12, 20263753Month endOct 31, 2026
4Lake CabinBrooksOct 16, 2026Oct 23, 20268057Lake Cabin blocked nights3
5Lake CabinOrtizOct 30, 2026Nov 2, 20263902City Loft blocked nights0
6City LoftKimOct 1, 2026Oct 4, 20264203
7City LoftRossiOct 6, 2026Oct 11, 20266505
8City LoftAdeyemiOct 14, 2026Oct 16, 20262902
9City LoftLarsenOct 18, 2026Oct 25, 20269457
10City LoftMoreauOct 27, 2026Oct 31, 20265204
11City LoftSilvaNov 5, 2026Nov 8, 20264350

Download this sheet as an Excel practice file (.xlsx)

Step 1: Count booked nights inside the month

You rent out a cabin and a loft, and you keep a list of bookings with check-in and check-out dates. Check-out minus check-in is the number of nights, since the check-out day isn't a night anyone sleeps there. A 9th to 12th stay is 3 nights.

The hard part is stays that cross into another month. The Patels checked in on Sep 27 and left on Oct 3: 6 nights, but only 2 of them in October. The formula clips each stay to the month: MAX(C2,$I$2) is the later of the check-in and the 1st, MIN(D2,$I$3+1) is the earlier of the check-out and the 1st of next month, and the gap between them is October's nights. MAX(0, ...) turns stays entirely in another month (like Silva in November) into 0 instead of a negative number.

Put this in column F and fill down. Change the month in I2:I3 and every row recounts.

=MAX(0,MIN(D2,$I$3+1)-MAX(C2,$I$2))

Result: 2

2: the Patels' Sep 27 to Oct 3 stay has 2 nights in October (Oct 1 and Oct 2).

Step 2: Available nights and the occupancy rate

Available nights are the nights you could have sold: 31 days x 2 properties = 62. Take off nights that weren't for rent, like the 3 nights the cabin was closed for a repair. That leaves 59.

35 booked nights / 59 available is 59.3%. If you forget the blocked nights you get 35 / 62 = 56.5%, which makes a good month look worse. Your own stays count as blocked too, not as empty nights.

I3-I2+1 gives the days in the month from your two date cells, or use =DAY(EOMONTH(I2,0)) if you only keep the start date.

=SUM(F2:F11)/((I3-I2+1)*2-SUM(I4:I5))

Result: 0.59322033898 shows as 59.3% with Percent format

About 0.593: 35 booked nights out of 59 available, so 59.3%.

Step 3: Occupancy for each property

A combined rate can hide a weak property. SUMIFS adds one property's nights, and you divide by that property's own available nights. The cabin booked 14 of its 28 open nights (50%), the loft 21 of 31 (67.7%).

Copy the formula for each property, or list the property names in a small table and point the SUMIFS at the name cell. With more than a handful of rentals, a pivot table on columns A and F does the nights part for you.

=SUMIFS(F2:F11,A2:A11,"Lake Cabin")/((I3-I2+1)-I4)

Result: 0.5 shows as 50% with Percent format

0.5: the Lake Cabin was booked 14 of its 28 available nights.

Step 4: Average nightly rate and revenue per available night

Occupancy alone can mislead: you can fill every night by pricing too low. Two more numbers help. The average daily rate (ADR) is revenue divided by booked nights. Revenue per available night (hotels call it RevPAR) is revenue divided by available nights, so it rewards both filling nights and the price you got.

Stays that cross months need their revenue split too. This formula divides each booking total by its nights and multiplies by the nights inside October, so the Patels' $690 for 6 nights counts as $230 in October. October comes to $4,495. ADR is $4,495 / 35 = $128.43, and revenue per available night is $4,495 / 59 = $76.19.

If your booking totals include a cleaning fee, decide whether it belongs in ADR. Many hosts keep it out so the nightly price is comparable between short and long stays.

=SUMPRODUCT(E2:E11/(D2:D11-C2:C11),F2:F11)/((I3-I2+1)*2-SUM(I4:I5))

Result: 76.186440678

About 76.19: $4,495 of October revenue over 59 available nights.

Common mistakes

Counting the check-out day as a night

Nights = check-out - check-in. Adding 1 overcounts every stay: October would show 44 nights instead of 35.

Giving a cross-month stay to the check-in month

Clip each stay to the month with MIN/MAX so its nights land in the months they happen.

Leaving blocked and owner nights in the denominator

Subtract nights that weren't for rent, and say so next to the rate.

Cancelled bookings still in the list

Add a Status column and use SUMIFS with Status "Confirmed", or move cancellations to another sheet.

FAQ

What is the formula for occupancy rate?

Occupied nights (or units) divided by available nights (or units), as a percentage. For nightly rentals that's booked nights / (days in period x properties - blocked nights). For long-term rentals it's usually occupied units / total units.

How do I calculate occupancy for long-term rental units?

Count the units marked occupied and divide by all units: =COUNTIF(F2:F13,"Occupied")/COUNTA(A2:A13). Our Excel for real estate page has a free rent roll template that does this.

How do I calculate occupancy for a whole year?

Use the same formula with I2 as Jan 1 and I3 as Dec 31. The MIN/MAX clipping works for any period, and I3-I2+1 gives 365 or 366 days.

What's the difference between ADR and RevPAR?

ADR divides revenue by nights you sold. RevPAR divides it by nights you could have sold, so it equals ADR x occupancy. Here $128.43 x 59.3% is about $76.19.

Functions used

Related guides

Updated