Managing room availability manually can quickly become confusing, especially when you handle multiple rooms, overlapping dates, and different booking statuses. To solve this, I’ve created a Reservation and Booking Status Calendar Template in Google Sheets that visually shows room availability using spreadsheet cells as rooms and color-coded highlights for bookings.
This template uses a combination of Google Sheets formulas and conditional formatting, with performance carefully considered during design. All formulas are now array-based, optimized to work efficiently even with large datasets.
Since step-by-step how-to guides are the highlight of InfoInspired, this post doesn’t just give you the download link. It also explains how the template works, how to use it effectively, and how you can customize or simplify it based on your needs.
Why This Reservation and Booking Status Calendar Template Is Different
This post is useful for two types of users:
- Beginners:
If you’re not comfortable with advanced Google Sheets formulas, you can use the template as-is. This guide includes written instructions as well as a video tour at the end to help you get started. - Intermediate / Advanced users:
If you already understand Google Sheets formulas, you can follow the how-to sections to learn how the template works internally. This allows you to remove unwanted features and further improve performance based on your specific needs.
Who Should Use This Google Sheets Booking Template
The Reservation and Booking Status Calendar Template in Google Sheets is designed for hotel room booking and single-property management.
It works well for:
- Small hotels
- Guest houses
- Boutique hotels with 5 to 20 rooms
- Properties with up to 25 rooms
You can add a few more rooms, but doing so may affect performance. If you manage a large property with many rooms, a dedicated Property Management System (PMS) is a better choice than spreadsheets.
Download the Google Sheets Reservation & Booking Status Calendar Template
Before continuing, download the template so you can follow along with the explanations below.
Key Features of the Reservation and Booking Status Calendar Template
Below are the key features that make this reservation and booking status calendar template in Google Sheets practical, flexible, and performance-optimized for small to medium properties.
- Displays three months of booking data based on a selectable start date
- Built-in support for 25 rooms (you can use fewer or add a few more)
- Supports ~2000 booking entries (1998 rows + header)
- Optimized for performance at this row limit (recommended sweet spot)
- Separate colors for:
- Confirmed bookings (green)
- Tentative bookings (orange)
- Subtle highlight for the last night of each booking, making consecutive bookings easier to distinguish
- Option to turn last-night highlighting ON / OFF
- Guest name displayed on the check-in date
- Automatically detects overlapping bookings and highlights them visually to help prevent double-bookings
Sheets Used in the Template
The file contains two sheets:
- Availability
- Reservations
Each sheet serves a specific purpose.
1. Availability Sheet – Customization & Usage
The Availability sheet is the homepage of the reservation and booking status calendar template.
Booking Grid
- Room names are listed on the left (rows)
- Dates are displayed across the top (columns)
- Each cell (D4:CQ28) shows the booking status of a room for a specific day
Room Details
There is sample data in the range A4:C28. Replace it with your own:
- Column A – Room Number (mandatory)
- Column B – Room Type (optional but recommended)
- Column C – Smoking Allowed (optional)
The template is pre-configured for 25 rooms. You can add more by scrolling down and using the Add rows option, but doing so may affect performance.
Date Range Selection & Last-Night Highlight Toggle
The date range displayed in the calendar is controlled by cell D1. Simply double-click the cell and select a start date using the date picker, and the sheet will automatically display bookings from the Reservations sheet for the next three months.
If you want to visually distinguish consecutive bookings, use the checkbox in cell V1 to turn the subtle last-night highlighting on or off. The Reservations sheet, where all booking data is entered, is explained next.
2. Reservations Sheet – Entering Booking Data
The Reservations sheet is where all booking entries are recorded. It contains 2000 rows, optimized for performance.
Main Input Columns
The following columns are mandatory and are highlighted with a green header:
- Room – Select a room number from the dropdown (linked to the Availability sheet)
- Name – Guest name
- Check-in Date
- Check-out Date
- Status – Select either Confirmed or Tentative
Important booking logic: If a guest checks in on 25-01-2026 and checks out on 27-01-2026, the room is occupied for two nights (25th and 26th). The check-out date itself is not blocked, which means the same room can be booked again starting on 27-01-2026.
Note: Check-in dates are inclusive, and check-out dates are exclusive.
Formula Columns (Do Not Edit)
Columns with a yellow header contain formulas:
- ƒ(x) Room Type – Automatically fetched from the Availability sheet
- ƒ(x) Nights – Calculated as check-out date minus check-in date
- ƒ(x) Duplicate – Identifies overlapping or duplicate bookings
These columns are protected to prevent accidental edits. If you try to modify them, a warning message will appear.
All other columns are optional and can be used to store additional booking details such as guest address, contact information, or notes.
How to Use the Reservation and Booking Status Calendar Template
Here’s how to start using the Reservation and Booking Status Calendar Template in Google Sheets. For additional clarity, you can also watch the video tour at the end of this post.
- Enter your room details in Availability!A4:C28.
- Go to the Reservations sheet.
- Delete only the sample data, column by column, from the green-header input columns.
- Do not delete any rows.
- Avoid selecting multiple columns at once, as this may include protected formula columns and trigger a warning.
- Enter the following booking details:
- Room number
- Guest name
- Check-in date
- Check-out date
- Booking status
- Return to the Availability sheet and set the calendar start date (for example, 01-Jan-2026) in cell D1.
- View bookings visually across the three-month calendar.
If overlapping dates are entered for the same room in the Reservations sheet, both entries are highlighted in red within the Reservations sheet to warn you of a duplicate booking.
When You Reach the 2000-Row Limit
Once you reach approximately 2000 booking entries in the Reservations sheet:
- Make a copy of the file
- Clear the booking data in the green-header columns
- Manually re-enter only the active bookings
Tip: Avoid copy-pasting booking data, as it may break formulas or conditional formatting.
Formulas Powering the Reservation and Booking Status Calendar Template
In this section, we’ll break down the key formulas used in both the Availability and Reservations sheets and explain how they make the calendar automatically track bookings, highlight duplicates, and display availability.
1. Reservations Sheet Formulas
Room Type Lookup (B3)
=ArrayFormula(XLOOKUP(Reservations[Room], Availability!$A$4:$A, Availability!$B$4:$B,))Automatically fetches the room type based on the selected room in the Reservations sheet.
Number of Nights (F3)
=ArrayFormula(Reservations[Check-Out Date]-Reservations[Check-In Date])Calculates the total nights booked by subtracting the check-in date from the check-out date.
Duplicate Booking Detection (N3)
=LET(
seq, SEQUENCE(ROWS(A3:A)),
MAP(
A3:A, D3:D, E3:E, seq,
LAMBDA(r, ci, co, s,
COUNTIFS(A3:A, r, D3:D, "<"&co, E3:E, ">"&ci, seq, "<>"&s)>0)
)
)Returns TRUE if any overlapping bookings exist for the same room, helping to prevent double-booking.
Availability Sheet Formulas
3-Month Date Timeline (D3)
=SEQUENCE(1, DAYS(EDATE(D1, 3), D1), D1)Populates the calendar with a sequence of dates starting from the date in D1, covering the next three months.
Day Names (D2)
=ARRAYFORMULA(IF(LEN(D3:3), TEXT(D3:3, "ddd"),))Displays the day of the week (Mon, Tue, Wed, etc.) for each date in the calendar.
Guest Name on Check-in Date (D4)
=LET(
dt, D3:3,
room, A4:A,
guest, Reservations[Name],
r_room, Reservations[Room],
start, Reservations[Check-In Date],
MAP(dt, LAMBDA(c, MAP(room, LAMBDA(r,
TEXTJOIN(" ?", TRUE, IFNA(FILTER(guest, (r_room=r)*(start=c))))
))))
)Automatically fetches and displays the guest name on the check-in date for each room in the calendar.
Conditional Formatting Rules Explained
Conditional formatting is what makes the hotel room reservation and booking status template visually intuitive. Below is a breakdown of the rules in each sheet and what they do.
Reservation Sheet
There are two conditional formatting rules in this sheet:
1. Invalid checkout date
=AND(E3<>"", F3<1)Highlights rows where the check-out date is missing or earlier than the check-in date, helping prevent invalid entries.
2. Duplicate booking highlight
=$N3Highlights rows where the Duplicate Booking Detection formula (in column N) returns TRUE, warning you of overlapping bookings for the same room.
Tip: To view all conditional formatting rules, click the row and column intersection at the top-left corner of the sheet to select all cells, then go to Format > Conditional Formatting.
Availability Sheet
There are four conditional formatting rules in this sheet:
1. Tentative Booking (Orange)
=COUNTIFS(
INDIRECT("Reservations!D3:D"), "<=" & D$3,
INDIRECT("Reservations!E3:E"), ">" & D$3,
INDIRECT("Reservations!A3:A"), $A4,
INDIRECT("Reservations!G3:G"), "Tentative"
)Highlights tentative bookings for each room and date in dark orange, showing which days are tentatively occupied.
2. Confirmed Booking (Dark Green)
The same formula as above, but the criterion “Tentative” is replaced with “Confirmed” to highlight confirmed bookings in dark green.
3. Last Night Highlight (Tentative, Light Orange)
=AND(
$V$1,
COUNTIFS(
INDIRECT("Reservations!G3:G"), "Tentative",
INDIRECT("Reservations!A3:A"), $A4,
INDIRECT("Reservations!E3:E"), D$3+1
)
)Highlights the last night of a tentative booking in a subtle orange color, helping visually separate consecutive bookings.
4. Last Night Highlight (Confirmed, Light Green)
The same formula as above, but “Tentative” is replaced with “Confirmed” to highlight the last night of confirmed bookings in subtle green.
Video Walkthrough: For a quick, step-by-step visual guide, watch the full video tutorial below to see how to set up and use the Reservation and Booking Status Calendar Template.
Final Thoughts
This Reservation and Booking Status Calendar Template in Google Sheets is ideal if you want a visual, spreadsheet-based booking system without relying on expensive PMS software.
You can:
- Use it out of the box
- Customize it deeply
- Strip it down for better performance
- Learn advanced Google Sheets techniques along the way
Resources
If you’re looking for similar calendar and booking-related templates, explore these:
- Dynamic Yearly Calendar Template in Google Sheets: Free Template and How-To Guide
- Hourly Time Slot Booking Template in Google Sheets
- Appointment Schedule Template in Google Sheets
- Calendar View in Google Sheets (Custom Template)
- Excel: Hotel Room Availability and Booking Template (Free)
Check out more Google Sheets templates in our complete list.
Very nice, thanks for sharing!
Hey Prashanth,
First off, amazing job with this tool—seriously, hats off to you. I’ve used similar ones before, and this is by far the most impressive. Huge kudos!
That said, I’ve run into a bit of a weird issue. The color scheme randomly stops working, but it’s only affecting rows 9 and 5. Everything else works perfectly fine. I’ve spent hours troubleshooting, and I’m completely stumped.
Here’s the link to the sheet: [removed by admin]
Any help would be massively appreciated!
Thanks in advance! 🙏
Hey Lluis,
Thanks for your feedback!
When I filter the column in ‘Reservations’ for that particular room, I noticed that cells G109 and G110 have negative days. Once you correct those, the problem should be resolved.
Another notable point is the use of a large volume of data. It can slow down or stop working because I’ve used Lambda functions. I suggest making a copy of the sheet for old records and keeping only the required data in the working sheet.
Hello,
Thank you for this great sheet! The only issue I’m facing is that it works inconsistently. Often, the data I enter into the reservation tab isn’t displayed correctly in the availability tab. Sometimes it only shows the last night of the reservation, and other times the reservation colors don’t appear.
Hi Prashanth!
Thank you so much for your work! It has elevated my booking sheet to a higher level! I was previously using an intermediate sheet (quite cumbersome) to convert data from Reservations to Availability… it was terrible.
The array formula and mapping techniques were a great lesson for me!
I share the same concern as many others: double bookings! I’m looking for a solution to immediately identify double reservations within dates in the Availability calendar (such as highlighting cells in red), or even in Reservations, where any cell could be colored to prevent this issue.
Even if it makes the sheet heavy, what could be done?
Thanks a lot in advance!
Yohann
Hi Yohann,
Thank you for your feedback. There was a highlighting rule in the past to identify duplicates, but I removed it because it started to affect the sheet’s performance.
I’ll look into what I can do to resolve this issue in the future. Unfortunately, I don’t have an immediate solution at the moment.
I do have a similar template for Excel (Microsoft 365) users that can identify duplicates. You can find it by searching for “Hotel” on this site.
Hi!
Although this was posted quite a while ago, I just found it and love how it works. One thing I am wondering about, hopefully you can help me with it.
When I choose a start date in the availability view and there are ongoing reservations from before that date, it doesn’t show the names of the guests on these rows. How can I make sure the names are always visible, even when the start date of the reservation is before the start date of the selection in the availability view?
Many thanks!
You can do that by displaying booking names in all cells. Please check my answer to the user “Linkz” in the comment section.
Hi Prashanth,
Thank you for providing the template. I’ve downloaded it and customized it to create an equipment reservation sheet for our non-profit organization. Before proceeding further, I want to ensure I have your permission to make these modifications and utilize the updated Google sheet for our NPO.
I also have a question regarding the functionality of the sheet. Currently, users can input room check-in and check-out dates even if the room is already booked. For instance, if I reserve room S301A with a check-in date of 6/Nov/2023 and a check-out date of 10/Nov/2023, the system accepts these dates without any error indication. However, the error becomes evident only upon checking the Availability sheet.
Is it possible to implement a validation check for the check-in and check-out dates in the Reservation sheet to prevent such conflicts?
Thank you for your assistance!
Thank you for reaching out. I’ve added a new sheet to the template containing the fair use policy.
Regarding double booking, the intended process is to check availability before proceeding with booking. Initially, I utilized conditional formatting in the “Reservations” sheet to highlight such entries. However, it significantly impacted the performance of the sheets. Therefore, I decided to remove it.
Hi, how I can make so that the name of the guest is repeated in every single cell of the green stripe?
To ensure the guest’s name is repeated in every cell of the green/red (booking confirmed) stripe, I recommend replacing the formula in C4 with the following:
=ArrayFormula(LET(dt,C3:3,
room,A4:A,
guest,Reservations!$B$3:$B,
r_room,Reservations!$A$3:$A,
start,Reservations!$E$3:$E,
end,Reservations!$F$3:$F,
status,Reservations!$H$3:$H,
MAP(dt,LAMBDA(c, MAP(room,LAMBDA(r,
JOIN(" ?",IFNA(FILTER(guest,(r_room=r)*(status="Confirmed")*(GTE(c,start))*(LT(c,end)))))
))))
))
If you also want the name in the orange/red (tentative) stripe, remove the
(status="Confirmed")*part from the formula.Thank you!
Hello, I encountered an issue and I was hoping you could help me. A client has reserved more than 10 days, and the start day until the end day in the availability sheet cannot be seen on one page, causing the blue bar to be out of view. How can we position the blue bar so that it’s visible?
Feel free to share a copy via email or share the URL below. That will help me understand the real scenario.
Hello,
Thank you for the sheets and the effort you put into them.
I would like to ask for advice. I’m interested in counting the number of people in the hotel for each date, and if possible, separating those who have confirmed from those who are “tentative”.
Is there a way to accomplish this?
Thank you.
In a new sheet, enter the following formula in cell A1:
=ArrayFormula(QUERY(SPLIT(FLATTEN(Reservations!H3:H&"|"&MAP(Reservations!E3:E, Reservations!F3:F,
LAMBDA(x, y, SEQUENCE(1, y-x+1, x)))),"|"),"SELECT Col2,
COUNT(Col2) WHERE Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1", 0)
)
Please note that this is a resource-intensive formula that will possibly return a three-column output. The first column will consist of date values. Select the first column and apply Format > Number > Date.
The formula calculates the number of nights booked for each date, with confirmed and tentative bookings displayed in separate columns.
Thank you for your quick reply,
I would prefer something that can be shown directly on the Availability screen. In my situation, I want to include three lines above the room number and booking calendar (lines 4, 5, and 6). Line 4 should display the total, line 5 should display the number of confirmed bookings, and line 6 should display the number of tentative bookings. I want every line to be filled, even when the number is 0.
Sorry, I don’t know how to send an image of what I want, but I hope I explained it correctly.
You can potentially utilize my above formula as the source and lookup values from it using XLOOKUP in the Availability Sheet. However, I am not in favor of using complex formulas since the sheet is already heavily loaded with functions and highlighting rules.
Thank you very much
Hi Prashanth,
Thank you for providing the calendar template. Is it possible to modify the check-in/check-out date format to mm/dd/yy? I attempted to change the format, but it resulted in an error.
Additionally, could we explore the option of adding more statuses (Confirmed/Tentative)? I’m interested in incorporating statuses for Male, Female, and Private Room.
You can adjust the date format by following these steps:
Click on File > Settings and change the Locale to United States. Save the Settings.
Next, select cells E3:F in the “Reservations” sheet and click Format > Number > Date.
Please note that adding extra statuses is not recommended, as it necessitates additional highlight rules and could potentially impact performance.
Any suggestions on how to edit it to manage bookings with hourly slots?
Sure, I’ll work on a template for managing bookings with hourly slots. I’ll post it as soon as possible. Please stay tuned!
Please check out this Hourly Time Slot Booking Template in Google Sheets.
The calendar template you provided is wonderful, but I’m aiming for a more detailed view. While the three-month calendar layout is helpful, I was wondering if there’s a version that offers a weekly overview with a more detailed breakdown of days by hours. Having the ability to view and manage bookings on a daily basis with hourly slots would be incredibly beneficial.
I apologize. Currently, such a template is not available. I will consider this for future development.
Hi everyone,
I’ve updated the reservation and booking status calendar template, adding new features and removing the helper tabs to make it more user-friendly.
Please check it out and let me know what you think!
Hi Prashanth,
I am writing to you today to inquire about a feature that would allow me to detect room overlaps in the Tab Availability sheet.
For example, if I have a room that is booked for the dates of 06/07/2023 – 10/07/2023 and then I book the same room for the dates of 08/07/2023 – 12/07/2023, the Tab Availability sheet would not currently show this as an overlap.
I would like to know if there is a way to modify the color of the cell if there is an overlap in room bookings. This would help me to easily identify any potential conflicts.
Thank you for your time and consideration.
Hi Dewa,
I understand your concern. I don’t want to add any more formulas to the sheet because I’m concerned that adding more formulas could affect the performance of the sheet.
Thank you for your understanding.
Hi,
Thanks for this! Really helpful.
By the way, how to expand this to cover say, 4 time block for each day?
As I’m trying to figure out a schedule for a car rental
Hi, mjj4884,
This template is not suitable for that.
Thanks.
Hi, Prashanth!
My table breaks when entering more than 20 rows of data. Why can this be? Instead of filled-in cells, I get numbers in them, and formulas are not fully loaded.
Hi, Nar,
Thanks for sharing your example Sheet. But it doesn’t reflect the problem.
Also, I suggest you remove the rule for highlighting in “Filtered” for improved performance.
After I try to add data to the table, there is an infinite loading.
Hi, Nar,
That might be due to the conditional formatting mentioned earlier. I’ve removed that. Please try again.
Hi, Prashanth!
Thank you for your work!
Hi Prashanth. Thank you for that, but there is still a glitch in one of the rooms in row 5 where it appears to be continuously booked ( all boxes are orange/yellow) even though it isn’t.
Waiting for your response within the Sheet.
Resolved. Thanks for your support.
Thank you for your time and effort Prashanth
– URL removed by the Admin –
The dates in the “availability” and I and J columns in “reservations” do not correspond to the dates I filled in.
I have also sent you emails.
Hi, Nick,
Please share the URL with Editable rights. Also, I haven’t received your email.
Thanks. I got the file and updated the formulas to cover six months of data.
Hi Prashanth,
The file won’t take any dates of reservations after line 21.
The dates I put in the reservation section/spreadsheet do not come in the availability section/spreadsheet.
Hi, Nick,
Can you share that file?
Share mockup data only.
Hi Prashanth,
Did you get the file?
I didn’t. You can share it (URL) below. I won’t publish that comment.
Hi Prashanth,
Thank you so much for this very handy tool. I would like to record my bookings for more than 3 months in advance, can you please advise how I could achieve this.
Will I need a different spreadsheet for each 3-month period, or is there a way to extend the duration?
Hi, Nick,
You can record your bookings for more than 3 months. The VIEW is only limited to three months.
I suggest you view the booking part by part. For example, enter 1-1-2023 in cell Availability!C1 to view the bookings from 1st Jan to 31st March.
Then enter 1-4-2023 in that cell to view the next three months’ bookings.
Thanks for the reply. My 3-month period is currently set at 17/3/2023 to 16/6/2023.
I tried changing the start date to 17/6/2023, but all my bookings from March were moved forward and appeared in June.
It seems I have messed something up.
Can I share the file with you so can take a look at it?
Please advise
Hi, Nick,
OK, if you want to visualize six months of data in one go, do as follows.
Note:- I don’t advise it as it can impact your Sheets’ performance.
1. Enter
=sequence(1,200)formula in Filtered!D2.2. Enter
=edate(C1,6)-1formula in Availability!G1.3. Replace
Filtered!$D$3:$CYpart of the Availability!C4 formula withFiltered!$D$3:$GU.Please try it and let me know.
Is there a way to keep the check-in and out-date cells blank (E1:F) in the reservations while maintaining the drop-down function and your custom formula?
I don’t want all dates showing until I input a value. I’ve tried adding a rule, but I keep breaking something.
Hi, Luca,
Sorry! I’m not clear 🙁
Great, I understand.
“You can use Data (menu) > Data Validation to avoid accidentally entering values.”
How do I do this exactly?
Hi, Ariel,
1. Click on cell D4
2. Go to the Data menu Data validation
3. Select “Add rule”
4. Apply to Range (as per my template range):
Availability!D4:CP13,Availability!C5:C135. Criteria: “Custom formula is”
And here is the formula to use there.
=not(len(D4))It may leave error symbols in highlighted cells. Just ignore it.
I hit delete in V5, and it worked! Thank you!
The thing is that I do not understand your first sentence, where I think you describe what the problem was.
What was wrong? How can I avoid this happening again in the future?
Thank you very much for your help!
Hi, Ariel,
You can find an array formula (expanding formula) in cell C4.
The value in cell V5, which you may have accidentally entered, prevented it from working.
Do not enter values in cells other than the specified area. You can use Data (menu) > Data Validation to avoid accidentally entering values.
Hi Prashanth,
Here it is:
* URL Removed By Admin *
I put dummy names and left a few records so you can analyze them.
Thank you very much, Prashanth. I look forward to seeing what the problem is and solving it!
Keep up your great work,
Ariel.-
Hi, Ariel,
All the cells except C4 in the range Availability!C4:CN9 must be blank.
Go to Availability!V5 and hit the Delete button. That may solve the issue.
Hello Prashanth,
Thank you for your reply!
I read your reply to Kerim, but I do not see a solution to my problem.
I can see the red highlight (last day of occupancy) but not the highlight for the other days. I think Karim is having another problem.
Would it be possible to send you the link so you can take a look?
Thank you very much!!
I look forward to using this great tool once it´s working again.
Best regards,
Ariel.
Hi, Ariel,
Please send it but only with a few records for fast-loading the file.
Also, replace real names with dummy names.
Hello Prashanth,
First of all, thank you for your work.
I have a problem with the availability sheet because it shows the reserved days and then not anymore (just the red one showing the last night).
Could you please help me? THANK YOU!!
Hi, Ariel,
Please go through my reply to Kerim.
Amazing template Prashant, thank you.
I successfully multiplied everything except the last night highlighting in red color. I can’t seem to find where the problem is. This is the only problem that holds me from using it. Could you please have a look?
— URL removed by Admin —
Hi, Kerim,
The issue is the special characters in room numbers.
Also, to improve the performance of the Sheet, you can remove the highlighting in the “Filtered” tab.
Hell,
Thank you for this template. I have a suggestion for improvement.
Sometimes we have rooms in the hotel for monthly rent, and I need a table that shows me the availability of rooms on a monthly schedule in the same way the daily schedule was shown.
Hey Prashanth,
A very nice template you’ve made.
However, I found an issue when there is a reservation on the last day that is displayed on the availability sheet.
Example:- When the displayed date is 1-Dec-2022 to 28-Feb-2023, and there is a reservation on the 28th Feb 2023, the whole row on the availability sheet will show the number 0.
Any idea on how to solve this?
Thank you.
Hi, Abu,
The answer lies in row#5 in the “Reservations” tab. Please check G5 there.
The start and end dates must be two different dates to get at least one night’s stay.
Hey Prashanth,
First of all, you are doing great.
I have been using your sheet for the last two months, and it’s been very useful for me.
Many Thanks😃
Hi, Mohamed,
Good to hear that 🙂
What to do when a customer does not give a checkout date? how to display ?
Hi all,
Now there are no drag-down formulas in the template! I’ve updated all of them using the new LAMBDA Helper functions.
Thanks.
Sounds great, Prashanth. What do we need to do to update our existing sheets?
Hi, Anita,
Remove all the formulas in Filtered!D3:D and Availability!C4:C.
Then copy-paste my new array formulas in Filtered!D3 and Availability!C4 from my sample sheet.
Hey Prashanth,
Firstly, great sheet!
We have a small rental shop, and this sheet is just (almost) perfect for us. So thanks!
Is it possible to add the time for check-in and time for check-out, it would help us a lot. A row more is OK, 24-hour time scale.
Also, the Update on April 6, how can we change the formula so the last day (the red color) will actually show on the last day of the booking? Not the night/day before as your formula show. Thanks again!!
Hi Prashanth,
I have been using your template for the last two months, and it’s been so useful.
Now I seem to have an issue. The yellow occupied squares do not show on the Availability tab for some of the rooms.
Could you please have a look and guide me? I’m enclosing the link to my sheet.
— URL of the Sheet removed by admin —
Hi, Anita,
The main issue is the missing formulas in some of the cells in Availability!C4:C.
Once you correct that, the highlighting will re-appear.
Another issue you are having is the duplicate entries in the Reservations tab. For example, refer to rows 49 and 51 (room # 7-1).
You can easily find them from the Filtered tab (red highlighted).
Thank you so much for this spreadsheet! It’s been amazing!
I have, however, run into the issue where a random cell on the “Availability” sheet will turn red. It is not reserved, and it does not have dates for those days. Do you know how I can fix this?
SUPER appreciate any insight into fixing this issue. Thanks again!
Hi, Natasha,
Could you copy that sheet and share its URL with me in your comment/reply below?
— URL removed by Admin —
Here you go! I’ve been trying to troubleshoot the problems more. So you can see it currently in cells AS7 and BL8.
Hi, Natasha,
Thanks for sharing your Sheet.
It’s viewable only. So do as follows to solve the issue caused by using sequence numbers as room numbers.
Go to Availability!C4.
Then go to Format > Conditional formatting.
Click on the Red highlighted conditional format rule.
Edit the formula (replace
$A4&C$3with"^"&$A4&C$3&"$"). You can also copy-paste the formula from my post which is updated now.Thanks for letting me know about the problem.
Hey hey! Thanks for your continued help with this!
I just made a copy of the sheet, and unless I’m looking at it wrong, it looks like there are red cells in the middle of reservations in cells 010 and a random one in X4.
Are these supposed to show up?
Hi, Natasha,
Both are correct.
X4 – (ref.: Reservations!F4) check-in on 22/11/2019 and check out on 23/11/2019. So one-night stay on 22/11/19, and that cell got highlighted in red.
O10 – (ref.: Reservations!F11) check-in on 10/11/2019 and check out on 14/11/2019. So last night’s stay was on 13/11/19, and that cell got highlighted in red.
The real issue was the partial matching of room numbers and that was solved with my previous update.
You do not require to copy my Sheet and start again. Just update the formula in your Sheet as recommended.
Hi Prashanth!
Thanks a lot for that template. It’s working great.
I have a use case a bit different than hotel room reservations.
I rent spots (for vans), and there can be up to 3 reservations per spot on the same day.
So, in the Availability tab, I would like to display a number telling me if there are 1, 2, or 3 reservations on that day.
Do you think I could achieve it with your template? Any advice?
Thanks again!
Hi, Laura P,
Sorry! It won’t work.
Hello!
This is a fantastic template and easy to use for beginners like me.
I wonder why the colors on the “Availability” sheet aren’t suddenly working anymore?
Is there a way to check that the formula is still correct? Thank you!
Hi, Patricia Swenson,
There might be errors on the other tabs. If you want me to check, share a copy of the hotel room booking template after removing personal info if any.
Hello,
This is great. I’m just having trouble with a couple of things.
If I put any reservations on the reservations tab lines 19-22, it creates an error that says, “Function SPLIT parameter 1 value should be non-empty.”
Also, it would be wonderful if the availability calendar turned a different color if you accidentally double-book a room.
Why do Reservation tab columns I and J have different dates than what I input in columns E and F?
Thank you for building this!
Hi, Nikki M Calles,
To solve the Split error, go to the ‘Filtered’ tab and copy-paste the D3 formula down as far as you want.
Regarding accidental double booking, please see the last part of the post for an update.
Thank you!
Hi Prashanth,
Thanks for the reply, I think I did not explain my query clearly… I mean the overlapping of check-in and check-out booking.
What I mean is, for example, our check-in is 2 PM (January 1), and check-out is 12 PM the next day (January 2). Then right away there is another guest check-in on the same day (January 2) at 2 PM… As it is, the day is only highlighted as red regardless if there is a guest booking right away or not…
IF we could add one more color we could easily distinguish if that day is checkout only and no guest right away (RED) or… another color indicating that there is another guest (PURPLE maybe) who has booked the room the same day.
Hi, Myson Mark,
Thanks for your explanation. I could understand it clearly from your last comment itself.
If the check-in is 2 PM (January 1), and check-out is 12 PM the next day (January 2), my template will only highlight January 1st that also in red color to indicate that is the last night of that booking. January 2nd will be blank even if it’s the check-out day.
We are just highlighting (counting) the nights. So, there is no questioning of overlapping.
Please check whether you are using my latest template as I have corrected the sheet recently based on user inputs.
If you go through the comments, you might find similar requests from other users which are finally sorted out using the above method.
Thanks a lot Prashanth 🙂
Hi Prashanth,
Thanks for the template.
Is it possible to add one more color for situations where a room check-in and check-out happen on the same day?
Hi, Myson Mark,
That issue is not arising. For example, one guest has booked room # 301A from 10/11/19 (check-in) to 14/11/19 (check-out).
That means four nights’ stay.
On the ‘Availability’ tab, the cells under the dates 10, 11, 12, and 13 (4 nights) will be highlighted, and 13/11/19 (not 14/11/19) will be highlighted in red to indicate it’s his last night.
The date column 14/11/19 will be blank and available for booking.
Hi Prashanth,
I have added more rooms in Availability and dragged the formula in C3 down. But I am getting the #NUM error “Function SEQUENCE parameter 2 value is 0.”
This error shows in one row. The others are fine.
Hi, Anita,
The said error is carried from the ‘Filtered’ tab column D. But no changes required in this tab or in the ‘Availability’ tab.
The reason for the error probably lies in the “Reservations” tab.
There, in columns E and F, you might have entered the same dates for check-in and check-out in one row.
There must be at least one night’s stay. You can find that by checking column G for 0 (zero) room nights.
Thank you, Prashanth! It works beautifully, and I’ve got the hang of it now.
The new double-check-in red alert is very helpful. I’ve downloaded the template again and transferred all reservations to it. Is it possible for the template to auto-update at my end with all the new features you add when you update it at yours?
Also, I wanted to report that the new template is taking ages to calculate formulas. Is it possible to speed this up? I’ve got about 29 reservations on it so far.
And is it possible to see guest names on the Availability tab, perhaps as a popup when we hover or click on a colored block?
Once again, thanks for all your work on this.
Hi, Anita,
I am glad to hear that you liked the template.
For future updates, please bookmark this post and check the last part of it.
I’ll only update it if any user report issues.
The slow performance of the sheet is probably due to the new rule to highlight duplicates. It’s an optional rule. You can remove that by going to the ‘Filtered’ tab.
Finally, sorry to say, it’s not possible with the current settings to add guest names to the Availability tab. Any further modifications to the template may further deteriorate its performance.
So I accidentally deleted the formula for the room nights column. How do I fix this so it will show me how many nights someone is staying?
Hi, Melissa,
Go to the File menu and click Version history and revert to a previous version.
Will I lose the information I have in the file?
Hi, Melissa,
You can first make a copy of the file (File > Make a copy) and then go to version history.
So your data will be safe in the copy.
Hi, Prashanth.
This job could be for operational simplicity very useful, but there is an important detail that almost prevents its use.
Its purpose is to reserve overnight stays.
The availability calendar must be accurate and truthful because it serves as the main visual reference before a reservation is made.
The availability calendar distinguishes what is free from what is not. Due to the characteristics of the platform, each box can show a unique meaning.
The reservation in the calendar therefore should not occupy even the check-out date but only up to the cell that marks the last night booked.
Because the cell should be free for the new night, and the latter should be highlighted to separate contiguous reservations.
The last night of different colors (like the changes requested by Sandy) should exclusively mark the end – the last night – of a reservation.
The same result of the Reservations! Room Nights field showing the exact number of nights equals the number of cells that will mark the reservation. And not one more as is the case now.
I’ve tried but couldn’t get it.
Could you please explain how this can be achieved?
Thank you in advance.
Hi, Francisco,
Based on your request, I’ve updated the template.
There are two changes.
Updated the Filtered!D3 formula (I had suggested the same to Sandy).
Added a new highlight rule in the “Availability” tab to highlight the last night before the check-out day.
N.B.:- Offered a similar formula rule to Sandy also. But that time, we were trying to implement it on the check-out date, so not practical in use).
Thanks for your suggestions.
Thank you, Prashant!
This is it! I appreciate the modification.
I would like to propose one more improvement that would complete the information needed in Availability!
Maybe the row below the reservation could contain the client name if it’s not possible to do it in the reservation row itself.
Hi, Francisco,
That might be possible in the ‘Availability’ tab but not on the same highlighted cells, but below in a new row.
But lots of work to do like;
Insert new rows below each room, a new rule to skip highlighting in that rows, and several Vlookups.
So I am not attempting that.
Thanks for your feedback.
What if you wanted to change the Check-in/Check-out dates to times?
Hi, Beans, You might be able to do that with a different template.
The formulas in this hotel room booking template are for “days” only.
Hi Prashanth, thank you for this intuitive template!
I was wondering, if and how could this template be amended to display the name of the person booking “inside” the period of booking?
I apologize if this sounds ridiculous, and once again, thank you for this and the lessons.
Hi, Joseph,
Welcome!
That doesn’t work. I already have formulas in those cells for highlighting.
Hi Prashanth, I’m a Google sheets novice trying to see if this would work for our campground.
Is there a way to determine who is booked in a specific spot on a specific date other than scrolling through the reservation data?
Thanks for any info.
Hi, Theresa,
You might want to use the FILTER or QUERY function.
The following formula will return the booking details (name, check-in, and check-out dates) on a given date.
=filter({Reservations!A3:A,Reservations!E3:F},Reservations!E3:E=date(2019,11,16))
For further assistance, you may please share a mockup sheet in the reply below (I won’t publish it).
Hi Prashanth,
Thanks for sharing this!
I was wondering if it’s possible to add another legend beside Available and Not Available.
If yes, how do I go about doing it? It would be perfect if it still auto-populates, and I can select a different color.
Regards,
Hi, Angel,
If you want to highlight specific dates such as events/holidays, enter them as a list in a column and use a MATCH-based formula to highlight those dates.
To know how to code the rule, you may please check this guide – Fully Flexible Fiscal Year Calendar In Google Sheets.
Hi,
Found this as a new owner of a guesthouse and a novice on Sheets.
Is it possible to either change column C in the reservations sheet to be the number of guests or to add in a column for the number of guests?
It’s an important metric for us, but I don’t want to break any formulas!
Hi, Oliver,
You can change column C for the number of guests without breaking any formulas.
But when you add a new column, you should change the Query formula in cell A2 in the ‘Filtered’ tab.
=query(Reservations!A2:K,"Select A,J,K where J is not null and K is not null")Hi,
Thanks for the share, any update on the double booking fix?
Regards
Hi, Robin,
I have elaborated on all the possible fixes in answers to previous user comments.
You may please check that.
Hi,
In sheet, it is occupying the check-out date also (in the availability sheet).
It should not show check out as occupied in the availability sheet.
How can I fix it?
Hi, Anubhav Agrawal,
Please go through the comments by user Sandy and my answers to them.
Thank you very much, Prashanth, much appreciated.
Hi Prashanth,
I checked again, and yes it does sort columns A and B into ascending order but only for each type, so all cabin 1’s get sorted, then all cabin 2’s, etc.
What I was trying to do was to get column B to sort from the 1/3, 2/3, 3/3, 4/3, and so on, so I could see all the bookings for each separate day.
Thanks for your patience with this.
Sandy McCleary
Hi, Sandy,
This will do.
=query(Reservations!A2:J,"Select A,I,J where I is not null and J is not null order by I asc",1)You can start learning Query from my function guide. It’s very easy to learn.
Thanks, Prashanth.
I tried that and it sorted column A into ascending order, but the check-in dates didn’t get sorted, When the check-in dates do get sorted does the corresponding check-out date move with the check-in date?
Thanks, Sandy
Hi, Sandy,
The formula will first sort column A, then column I. So please once again check your result.
When sorting, the rows are rearranging. So no doubt, all the values in the rows will be moved.
Hi Prashanth
This is working really well now.
In “Filtered” is there a way of sorting Column B by the date eg 1/3/21 to 31/3/21. So it is a bit easier to see who is checking in on any particular day. I have tried using the sort function but it ends up in a mess.
Thanks, Sandy.
Hi, Sandy,
Try replacing the formula in Filtered!A2 with the following.
=query(Reservations!A2:J,"Select A,I,J where I is not null and J is not null order by A asc,I asc",1)It first sorts the room numbers in ascending order and then sorts the check-in dates also in ascending order.
Awesome, thank you.
Thanks for your reply Prasanth,
I have tried this out on the copy reservation sheet and I see what the drawback is, so I don’t think this will work for me.
I have thought of another workaround, Say someone books in from 6th March – 10 March, that is 4 nights in total, on the availability sheet it shows as 5 days.
Would it be possible to have the booking on the availability sheet show as 4 days, that would leave the 10th march showing as a free check-in day.
I’m not sure how that would work for an overnight booking though.
Otherwise, this is really great.
Thank you
Hi, Sandy,
That’s easily doable.
Just modify the formula in Filtered!D3 from
=if(len(A3),sequence(1,days(C3,B3)+1,B3),)to=if(len(A3),sequence(1,days(C3,B3),B3),).You must drag this formula down until you reach D100.
Hello
Re Camilla’s question regarding setting the checkout day to a different color is it possible to do this, it would be great to be able to see when a customer checks out and another check-in on the same day.
Thank you
Hi, Sandy,
Please try this.
Select the range C4:CP13 in the “Availability” tab. Go to “Format” > “Conditional format”.
You can see the two existing rules. Add the below new format rule.
=ARRAY_CONSTRAIN(sort(REGEXMATCH(INDIRECT("Reservations!$A$3:$A$17")&INDIRECT("Reservations!$J$3:$J$17"),$A4&C$3),1,0),1,1)
Set the highlighting to Red (both fill color and font color).
Now please make sure that there are a total of 3 rules for the range “Availability!C4:CP13”. The above formula rule should be on the top (not the bottom). If not, you can drag the formula rule to the top.
It will highlight the check-out day in the red color.
It will help you to “partially” understand whether the check-out/check-in days are on the same day.
Here is the draw-back.
Eg.:
If one person cheks-out today and another person checks-in the next day, you can’t differentiate because today will be in “Red”.
I have added more rows in the reservation sheet and now the room nights G3 will not calculate the number of nights. How can I fix this, please?
Hi, Sandy McCleary,
The formula will definitely work if you have room numbers in the new row.
Other than check-in and check-out dates (columns E and F), the new rows must contain room numbers in column A.
Note:- The formula in G3 is set for the whole column. So you should not copy this formula down.
Thank you, Prashanth.
Please could you tell me how I would go about creating a room “Camp Closed” and it will automatically change all rooms in that date range to unavailable
Hi Prashanth
Thanks so much for this! I am trying to add a column before column A for booking ref but when I do this it takes me availability off the calendar, can you help, please?
Hi, Nikki Smith,
I recommend using the template as it is. Inserting new columns may affect formulas in other tabs.
The reason, in my reservation and booking template I have used the Query function. In Query formulas, the column reference won’t change automatically in the SELECT clause as it’s used as a string.
If you insert a column before “Room #” (column A) in the ‘Reservations’ tab you must change the Query formula in “Filtered” tab cell A2 as below.
=query(Reservations!B2:K,"Select B,J,K where J is not null and K is not null")This may solve the issue.
Thanks for making this, what are the numbers that show in the filtered tab?
Hi, Adam,
In the hidden (grouped column) right?
That are dates expanded formatted as values.
Thanks for the speedy reply. Yeah the hidden grouped column, do they serve a purpose for the other tabs?
Hi, Adam,
It’s actually expanded check-in and check-out days and used for highlighting.
Hi! I just started using Google Sheets (never had that much experience with Excel as well) and this really helped me. I have one question though, how do I change their colors? cause I am color blind and I’m having a hard time hehe
Hi, Veronica,
To change the Orange and Light Blue colors, click the cell C4. Then go the menu Format > Conditional formatting. You can see two conditional format rules on the right-hand side of the screen. Click the rule that you want to change the color and change the fill color under the “formatting style”.
Similarly, you can change the Dark Blue color. For that, click cell C2 and follow the above steps.
Hi Prashanth,
Thanks for creating this. I’m just wondering if it’s possible to add in time too?
For instance, for hourly car rental. there will be multiple bookings in a day for certain cars alongside the daily/monthly rental cars.
Thanks so much.
Hello,
I am new to Google Sheets and was used to working on excel with macro VBA. I like very much your sheet but I have a question which I’m unable to find a solution.
Is it possible on the ‘Availability’ tab to show the guest’s name in the planning at the same time that you show the unavailable room? That will be really helpful.
Thanks for your help.
Regards
Hi, Philip,
It’s not possible as on the ‘Availability’ tab there is a formula in cell C4 which occupies all the cells (values are hidden though) in the range C4:CP.
Hello there,
Thanks for creating this. Is it possible to have a longer time period than three months, please? I would love to be able to do it for the whole year.
Thanks.
G
Hi, Georgia,
If I remember correctly, that you can do by changing the tiny formula in Availability!G1. That cell is protected so sheets may warn you when you attempt to edit the cell. You can ignore the warning and insert this formula.
=edate(C1,12)-1Wait for a few seconds for sheets to automatically increase the columns to expand the booking highlighting for the whole year. If it fails, hover your mouse pointer over the formula (error) to see the required number of columns to insert. Insert that many columns at the end of the sheet.
Similarly, go to the ‘Filtered’ tab and insert columns at the end. First, in that sheet, you may need to click the “+” group button at the top to expand the column group if not already. Then insert the required number of columns before the last column in that sheet.
Greetings! I have done this as well, thank you!
However, when I insert the Check-in and Check-out dates in the Reservations tab (dates that extend beyond 90 days), it doesn’t fully transfer over to the Availability Calendar. Is there a solution to this?
Thanks for all your help!
Side note: can you customize these spreadsheets for clients with specific needs?
Hi, Jenee,
You can view any three months’ data in the ‘Availability’ tab. Just change the start date in cell C1 in that tab.
I’ve put that restriction to make the Sheet responsive.
This is great!
I would also love an update if there would be a fix for double booking and indicator for same-day check-out x check-in. 🙂
Any update on the double booking?
Thanks for this it is awesome!
I have been trying to set the checkout day to a different color because there is no way to see when a room is being vacated on the same day that the other person checks in.
Thanks for letting me know about the ‘issue’. I’ll check it and update you.
Nice Job!
The problem is that if you are wrong in allocating rooms, like the same room on the same date for 2 guests, the availability shows nothing, so you can make a double booking!
Hi, Joel,
Thanks for your feedback!
My intention with the template is to make the booking staff know the availability (see the ‘Availability’ sheet) before booking.
I think I can address your said issue (wrong allocation) with data validation. I’ll go through it and if I find a solution, I’ll update here in the future.
Best,