Are you tired of the hassle of managing reservations and bookings for your hotel or business? Look no further! I have created a reservation and booking status calendar template in Google Sheets that will make your life a whole lot easier.
Features of the Template
This template is completely free and designed to be user-friendly, even for those with limited knowledge of Google Sheets. It allows you to easily check the availability of rooms on any particular date or date range.
Here are the key features:
- You can view three months of booking data based on a start date of your choice.
- The template includes rows for 50 rooms, but you can easily add more if needed.
- Conditional formatting is used to show the availability (light blue) or unavailability (orange, light green, or red) of rooms. The highlighting is based on booked nights. For example, if a room is booked from November 16, 2019 to November 22, 2019, the corresponding cells will be highlighted accordingly.
- The booking person’s name is displayed in the chart.
How to Use the Reservation and Booking Status Template
The template consists of two sheets within a file: Availability and Reservations.
1. Availability Sheet: Customize It to Meet Your Needs
The “Availability” sheet serves as the homepage of the booking and status calendar template. In this sheet, you can see the statuses of bookings and availability.
To customize this sheet:
- Replace the dummy data in cells A4:B53 with your own room numbers and room types (optional).
- If you have more than 50 rooms, simply add more rows at the bottom.
- The date picker in cell C1 controls the date range for the chart. Adjust it to your desired start date.
2. Reservations Sheet: Customize It to Meet Your Needs
The “Reservations” sheet is where you can enter your booking data. You can keep track of all your previous, current, and upcoming bookings here.
To customize this sheet:
- Select the room numbers in column A. The drop-downs will be populated with the room numbers from the Availability sheet.
- Enter the check-in date in column E and the check-out date in column F.
- Enter the booking name in column B.
- In column H, select the booking status from the options: Confirmed or Tentative. The bar colors will be light green and orange, respectively. If the booking is only for one night, the color will be red.
Please ensure that you avoid duplicate entries to accurately check the availability of rooms. Before entering each booking, check the availability chart in the Availability sheet and select the appropriate date in cell C1.
How to Create a Booking Calendar Template in Google Sheets
For those who are interested in the technical details, here are the formulas used in both the Availability and Reservations sheets and a brief explanation of each.
Formulas in the Reservations Sheet
The G2 cell in the “Reservations” sheet contains the following array formula, which calculates the number of days booked for each room based on the check-in and check-out dates:
=ARRAYFORMULA(VSTACK("Room Nights",IF(A3:A="",,DAYS(F3:F,E3:E))))
Formulas in the Availability Sheet
Formula to Populate the Date Range
The following SEQUENCE formula in cell C3 populates the third row in C3:CP3 with three months of dates (timescale):
=SEQUENCE(1,DAYS(EDATE(C1,3),C1),C1)
The formula calculates a date that is three months after the start date specified in cell C1. The SEQUENCE function then returns a sequence of dates starting from the specified start date.
Formula to Convert Dates to Days of the Week
The C2 formula converts the populated dates in C3:CP3 to days of the week:
=ARRAYFORMULA(IF(LEN(C3:3),TEXT(C3:3,"ddd"),))
Formula to Fetch Booking Names
The C4 formula in the “Availability” sheet fetches the booking names from the “Reservations” sheet and places them in the corresponding check-in date cells:
=LET(dt,C3:3, room,A4:A, guest,Reservations!$B$3:$B, r_room,Reservations!$A$3:$A, start,Reservations!$E$3:$E, MAP(dt,LAMBDA(c, MAP(room,LAMBDA(r, JOIN(" ?",IFNA(FILTER(guest,(r_room=r)*(start=c)))) )))))
Conditional Format Rules in the Availability Sheet
The template includes three conditional formatting rules for the range C4:CP53. The rules are applied based on the statuses and booking dates in the Reservations sheet. Here are the formulas used:
Booked Days (Orange)
=LET(from_to,"Reservations!E3:F", booked_room,"Reservations!A3:A", status,"Reservations!H3:H", room,$A4, dt,C$3, ftr,FILTER(INDIRECT(from_to),(INDIRECT(booked_room)=room)* (INDIRECT(status)="Tentative")), SUM(MAP(CHOOSECOLS(ftr,1),INDEX(CHOOSECOLS(ftr,2)-1,0), LAMBDA(st,en, N(ISBETWEEN(dt,st,en))))))
Booking Confirmation (Replaces Orange with Light Green)
The same “Orange” rule was copied and modified with the condition “Confirmed” replacing “Tentative”.
Last Night (Red)
=LEN(LET( dt,C$3, room,$A4, guest,"Reservations!$B$3:$B", r_room,"Reservations!$A$3:$A", end,"Reservations!$F$3:$F", IFNA(FILTER(INDIRECT(guest),(INDIRECT(r_room)=room)* (INDIRECT(end)-1=dt)))))
Consider the Check-Out Date (Not Last Night)
If you prefer to highlight the booking start date to the end date instead of the last night only, you can make a simple modification. Remove the “-1” from the orange, light green, and red highlight rules in the formulas mentioned above.
I hope you find this free reservation and booking calendar template helpful for your business! Give it a try and see how it simplifies your booking management process.
For more useful templates and guides, be sure to check out Crawlan.com!
