Occupancy rate is nights booked divided by nights available — simple, except that 'nights available' changes every month. Dividing by a hard-coded 30 overstates February and understates a 31-day month. This recipe pulls the real day count for any month with EOMONTH and DAY, so the same formula is correct in February and in August without editing it.
22 nights booked in a 30-day month is 73.3% occupancy; the same 22 nights in a 31-day month would be 71.0%.
The example
Three months with different lengths. The day count comes from the formula, not a typed-in constant.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Nights booked | Month start | Occupancy |
| 2 | Sep 2026 | 22 | 9/1/2026 | 73.3% |
| 3 | Oct 2026 | 18 | 10/1/2026 | 58.1% |
| 4 | Feb 2026 | 9 | 2/1/2026 | 32.1% |
The formula
EOMONTH finds the last day of the month; DAY converts that date into a plain day-count number:
How it works
Working from the inside out:
EOMONTH(C2,0)returns the date of the last day of the month that C2 falls in — for a September start date, that is 9/30/2026.DAY(...)strips that date down to just the day-of-month number: 30 for September, 31 for October, 28 or 29 for February depending on the year.B2/DAY(...)divides nights booked by that real day count instead of a constant, so the same formula is correct for every month without editing.
Format the result cell as a percentage; the formula itself just returns a decimal fraction like 0.733.
Try it: interactive demo
Enter nights booked and pick the month.
Variations
Occupancy across a custom date range, not a full month
For a rolling window (last 30 days, a quarter) divide by the actual number of days between two dates instead of a month boundary.
Blocked-off maintenance nights should not count as available
Subtract any nights you blocked the calendar yourself (cleaning buffer, owner use) from the denominator, or occupancy looks artificially low for nights that were never actually for sale.
Pitfalls & errors
EOMONTH requires the Analysis ToolPak in very old Excel versions (2003 and earlier); on any modern Excel or Microsoft 365 it is a native function and needs nothing extra.
C2 must be an actual date value, not text that looks like a date. A cell typed as the text "9/1/2026" left-aligned instead of right-aligned will make EOMONTH return an error.
Occupancy over 100% is a data problem, not a great month — it means nights booked was counted from a system that double-counts back-to-back reservations. Add a check that flags any occupancy result above 100%.
Practice workbook
Frequently asked questions
Why not just divide by 30 and call it close enough?
Does this handle a leap-year February automatically?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class