Hotel Booking Sheet with Availability & Occupancy

A hotel booking sheet checks room availability, records stays, and shows occupancy. You build it in Excel and use Copilot and ChatGPT for the availability lookup and the booking form.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It lists rooms, records bookings with check-in and check-out, and tells you if a room is free for a date range.
• Where it is used: Small hotels, guesthouses, and rental managers.
• What you learn: date overlap logic, COUNTIFS, price math, and AI-written VBA.

Features

• Room list with type and price
• Booking log with dates
• Availability check for a room and range
• Occupancy rate
• AI-written booking form

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• Developer tab for VBA

Folder Structure

hotel-booking-sheet.xlsx
│
├── Sheet: Rooms    (RoomID, Type, Price)
├── Sheet: Bookings (RoomID, CheckIn, CheckOut, Guest)
├── Sheet: Check    (availability lookup)
└── Module: HotelMacros (VBA)

• Rooms: the inventory.
• Bookings: every stay.
• Check: the availability tester.
• HotelMacros: booking code.

Step-by-Step Development

Step 1 – Rooms and bookings tables

What are we building?

Two tables: Room and Book.

Why do we need it?

Availability compares a request against booked ranges.

How do we build it?

Create both tables from your data.

=COUNTA(Room[RoomID])

• What it does: counts rooms.
• Why needed: base for occupancy.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"How do I make two related Excel tables, one for rooms and one for bookings?"

Step 2 – Availability check

What are we building?

A formula that returns "Booked" if any booking overlaps the requested dates.

Why do we need it?

You must not double-book a room.

How do we build it?

On Check, request Room in B1, Start in B2, End in B3.

=IF(COUNTIFS(Book[RoomID], B1, Book[CheckIn], "<="&B3, Book[CheckOut], ">="&B2)>0, "Booked", "Free")

• What it does: counts bookings for that room where the stay overlaps the request.
• Important line: CheckIn <= request end and CheckOut >= request start is the overlap test.
• Why needed: overlap means the room is taken.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Write a COUNTIFS that finds date overlap: same room, booked check-in <= request end and booked check-out >= request start."

Step 3 – Occupancy rate

What are we building?

A percentage of rooms occupied on a given night.

Why do we need it?

Occupancy is the hotel's key metric.

How do we build it?

=IFERROR(COUNTIFS(Book[CheckIn], "<="&B2, Book[CheckOut], ">="&B2)/COUNTA(Room[RoomID]), 0)

• What it does: counts rooms occupied on date B2 divided by total rooms.
• Important line: IFERROR avoids errors when no rooms exist.
• Why needed: a clean occupancy percent.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write an Excel occupancy rate: rooms occupied on a date divided by total rooms, with error handling."

Step 4 – VBA booking form

What are we building?

A form to add a booking.

Why do we need it?

Faster and safer than typing rows.

How do we build it?

UserForm with Room, CheckIn, CheckOut, Guest and a button.

Private Sub cmdBook_Click()
    Dim ws As Worksheet
    Set ws = Sheets("Bookings")
    Dim r As Long
    r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(r, 1).Value = txtRoom.Value
    ws.Cells(r, 2).Value = txtIn.Value
    ws.Cells(r, 3).Value = txtOut.Value
    ws.Cells(r, 4).Value = txtGuest.Value
    MsgBox "Booked"
End Sub

• What it does: appends the booking to the next blank row.
• Important line: txtRoom.Value reads the room from the form.
• Why needed: one click records a stay.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write VBA that adds a booking row with Room, CheckIn, CheckOut, Guest from a user form."

Complete Source Code

Check formulas

=IF(COUNTIFS(Book[RoomID], B1, Book[CheckIn], "<="&B3, Book[CheckOut], ">="&B2)>0, "Booked", "Free")
=IFERROR(COUNTIFS(Book[CheckIn], "<="&B2, Book[CheckOut], ">="&B2)/COUNTA(Room[RoomID]), 0)

VBA booking

Private Sub cmdBook_Click()
    Dim ws As Worksheet
    Set ws = Sheets("Bookings")
    Dim r As Long
    r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(r, 1).Value = txtRoom.Value
    ws.Cells(r, 2).Value = txtIn.Value
    ws.Cells(r, 3).Value = txtOut.Value
    ws.Cells(r, 4).Value = txtGuest.Value
    MsgBox "Booked"
End Sub

AI prompts

ChatGPT: "Two related tables rooms and bookings."
Copilot: "COUNTIFS date overlap same room."
ChatGPT: "Occupancy rate with error handling."
ChatGPT: "VBA add booking row from form."

Running the Project

1. Open hotel-booking-sheet.xlsx.
2. Enter rooms and bookings (or use the form).
3. On Check, enter a room and dates to see Free/Booked.
4. Expected output: availability and occupancy update live.

Testing

Test Case 1

Input: Room 101 booked Jan 1–5; request Jan 3–4. Expected Output: "Booked". Explanation: Request overlaps the stay.

Test Case 2

Input: Same room; request Jan 6–7. Expected Output: "Free". Explanation: No overlap.

Test Case 3

Input: 10 rooms, 4 occupied on Jan 2. Expected Output: Occupancy 40%. Explanation: 4/10.

Common Errors

Always Booked

Problem: Every room shows booked. Reason: Overlap test wrong (used < instead of <=). Solution: Use CheckIn <= request end and CheckOut >= request start.

Occupancy over 100%

Problem: Percent too high. Reason: Divided by wrong count. Solution: Divide by total rooms, not bookings.

Form error

Problem: Type mismatch on date. Reason: Text box held text, not date. Solution: Convert with CDate(txtIn.Value).

Improvements

• Add room type filter to availability
• Show price total for a stay
• Use Power Automate to confirm by email
• Ask ChatGPT for upsell suggestions
• Export occupancy report to PDF

Challenge Exercises

1. Add a total price for a stay (nights × room price via XLOOKUP).
2. Write a formula for the number of free rooms on a date.
3. Use ChatGPT to write VBA that blocks booking if overlap found.
4. Build a calendar grid showing each room's booked nights.
5. Let ChatGPT suggest dynamic pricing from occupancy.