Pivot Table Issues

How to Calculate Monthly Hotel Occupancy in an Excel Pivot Table

Algirdas Jasaitis Oct 21, 2026 868 views

Question details

The user needs to calculate hotel room nights booked and occupancy percentages per month using a pivot table, particularly for guest stays that span across multiple calendar months.

How to Calculate Monthly Hotel Occupancy in an Excel Pivot Table

Product: Excel
Device & OS: not provided
Scenario: Creating a monthly hotel occupancy report based on varying check-in and check-out dates.
Observed behavior: Standard pivot tables group data by a single date column (like check-in date), which inaccurately assigns all nights of a cross-month stay to the first month.

Before you start

Ensure your raw data includes distinct check-in and check-out date columns, and verify the total number of available room nights for your hotel for each calendar month.

Solution 1Recommended

Calculate Nights Per Month Before Creating the Pivot Table

To accurately report occupancy for stays spanning multiple months, you must calculate the exact nights spent in each calendar month within your source data before building the Pivot Table.

Because pivot tables summarize based on a single date field, reservations that start in one month and end in another cannot be automatically split by the pivot table itself. You need to add helper columns to your source data to distribute the room nights accordingly.

Add Month Boundaries

Add columns for the start and end dates of the reporting month you want to analyze, or use helper columns to represent 'Month 1' and 'Month 2' for stays that cross over.

Calculate Nights per Month

Use an IF or MIN/MAX formula to calculate how many nights of the stay fall into each specific month. For example, limit the check-out date to the end of the first month to count the first month's nights.

Add Available Room Nights

Create a helper column representing the total available room nights for that month (e.g., total rooms multiplied by days in the month) to serve as your baseline for the occupancy rate.

Insert the Pivot Table

Select your updated dataset, go to the 'Insert' tab on the ribbon, and click 'PivotTable'. Place the 'Month' field in the Rows area.

Add a Calculated Field for Occupancy

Drag the calculated 'Nights' to the Values area. Then, go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field, and create a formula like '= Booked Nights / Available Nights' to display the occupancy percentage.

Calculate Nights Per Month Before Creating the Pivot Table

Data Normalization Alternative: For large datasets, it is often easier to use Power Query to expand date ranges into individual daily rows before loading the data into a Pivot Table.

Efficient Data Management

Calculate Hotel Occupancy Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing tools, advanced Pivot Tables, and comprehensive date functions to easily manage complex hotel reservation data and calculate accurate occupancy rates.

1. Import Reservation Data: Open your hotel booking dataset containing check-in and check-out dates in WPS Spreadsheet.
2. Prepare Helper Columns: Add helper columns using formulas to calculate the exact number of nights spent in each specific calendar month.
3. Insert a PivotTable: Navigate to the 'Insert' tab, select 'PivotTable', and choose to place it on a new worksheet.
4. Summarize Occupancy: Drag the Month to Rows, Booked Nights to Values, and use the Calculated Field feature to generate your occupancy percentage.

100% compatible with Microsoft Excel (.xlsx) and CSV formatsAdvanced Pivot Table functionality with support for Calculated FieldsBuilt-in robust date and time functions for precise stay calculationsFree, lightweight, and user-friendly interface for quick reporting

microsoft office alternative - wps office

Frequently Asked Questions

Why does my pivot table assign cross-month stays entirely to the check-in month?

A pivot table groups data based on a single specific date field provided in the rows. If you use the check-in date as the row field, the entire duration is categorized under that initial month regardless of the check-out date.

How do I show percentages in an Excel Pivot Table?

You can add a Calculated Field by navigating to the PivotTable Analyze tab, selecting Fields, Items & Sets, and creating a formula dividing your total booked nights by total available nights. Once added, format the resulting value column as a percentage.

Can I automate splitting dates across months?

Yes, you can use Power Query to expand the start and end dates into a list of all dates in the stay, extract the month for each day, and then pivot the results for completely automated and accurate monthly reporting.

Excel & Data Problems