Reservation and Booking Status Calendar Template in Google Sheets

My reservation and booking status calendar template in Google Sheets uses spreadsheet cells as rooms and highlighting to show availability/non-availability.

I have used a few formulas and conditional formatting rules. All the formulas are array formulas, so you do not need to worry about copying and pasting when you add more data.

How-to guides are the highlights of this blog. So in this post, in addition to providing a link to my reservation and booking calendar template, I have also included the how-to part of it.

These have two benefits:

If you are not familiar with complex Google Sheets formulas, you can use the template as is. I have provided the instructions within the template and in this post.
If you have some idea about Google Sheets formulas, you can follow my how-to part to customize the template for your own different purposes.

My template is meant for monitoring hotel reservation/booking status, such as the availability and non-availability of rooms. However, you can use the template if you want to book anything similar, such as viewing the status of program/event booking or the status of rent-a-car booking.

First, get my Google Sheets reservation/booking status calendar template so that I can explain how to use it.

Hotel Room Booking

Reservation and Booking Calendar Template in Google Sheets: Features

First and foremost, this template is free and easy to use, even with limited Google Sheets (spreadsheet) knowledge. It also allows you to easily find the availability of rooms on any particular date or date range.

Reservation and Booking Status Calendar Template in Google Sheets

Here are the key features:

The template can show three months of booking data based on a provided start date. You can choose different start dates.
I have added rows for 50 rooms, but you can add more rows if you have more rooms.
I used conditional formatting to show the availability (light blue) or unavailability (orange, light green, or red) of rooms. Since this is basically a hotel room reservation and booking calendar template, it uses booked nights. So if a person booked a room from November 16, 2019 to November 22, 2019, the highlighting will be as follows:
- November 16, 2019 to November 20, 2019: 5 days in orange if the booking is tentative and light green if the booking is confirmed.
- November 21, 2019: 1 day in red. No highlighting will be on the 22nd as that day is available for the next booking. (You can modify these three rules to highlight the start date to the end date if your purpose is something different.)
The booking person’s name (guest) is shown in the chart.

The above are the key features of my reservation and booking calendar template in Google Sheets.

This makes it easy to find the availability of rooms on any particular date or date range.

How to Use the Reservation and Booking Status Template in Google Sheets for Your Business

The reservation and booking status calendar template in Google Sheets uses two Sheets within a file:

Availability
Reservations

There is also a third tab named “Notes,” which is self-explanatory. Let me explain what you may need to change/adjust in each sheet:

1. Availability Sheet: How to Customize It to Meet Your Needs

The “Availability” sheet is the homepage of the booking and status calendar template. In this sheet, you can see the statuses of bookings and availability in the range C4:CP53.

There is some dummy data in A4:B53. Replace them with your own data: room numbers (A4:A53) and room types (B4:B53), but room types are optional.

You can add up to 50 room numbers because the range in A4:A53 is 50 rows. If you have more rooms, simply add more rows at the bottom.

You only need to make one more change. There is a date picker in cell C1. This controls the date range for the chart C3:CP3.

2. Reservations Sheet: How to Customize It to Meet Your Needs

The “Reservations” sheet is for entering your booking data. You can keep all your booking data (previous, current, and upcoming) on this sheet.

You are required to enter or select the following data:

Pick room numbers in A3:A (The drop-downs will be populated with all your room numbers in Availability!A4:A53).
Enter the check-in date in E3:E and the check-out date in F3:F.
Enter the booking name in B3:B.
In cells H3:H, select the booking status from the two options: Confirmed and Tentative. Their bar colors will be light green and orange, respectively. However, if the booking is for one night, these rules will be overridden and the color will be red.

Please take extra care when entering data in this sheet. Avoid duplicate entries. For example, if a room is booked from January 1, 2023 to January 31, 2023, the same room should not be booked for any date in that date range.

This is important because the purpose of the reservation and booking calendar template is to check the availability of rooms.

To avoid duplicate entries, check the Availability sheet chart area for the room’s availability before entering each booking. You should also pick the appropriate date in Availability!C1.

Note: You can insert or delete columns only after column H.

How to Create a Booking Calendar Template in Google Sheets

In this section, I will share the formulas used in both the “Availability” and “Reservations” sheets and their purposes. I hope this will help you modify the formulas if needed.

1. Formula in the Reservations Sheet

In cell G2 of the “Reservations” sheet, I have the following array formula, which populates the number of days booked from the check-in and check-out dates of guests:

=ARRAYFORMULA(VSTACK("Room Nights",IF(A3:A="",,DAYS(F3:F,E3:E))))

Note: We don’t need this formula to create the bars in the “Availability” tab of the reservation and booking calendar.

Formula Explanation:

The DAYS function is used in an array formula to calculate the number of days between the check-in and check-out dates for each room.
The VSTACK function creates a vertical stack of two arrays: the first array contains the header text “Room Nights” and the second array contains the number of room nights returned by the DAYS function.
The IF function checks if the room number in column A is empty. If it is empty, the function returns a blank value. Otherwise, the function returns the number of days between the check-in date (column E) and the check-out date (column F) for that room.

2. Formulas in the Availability Sheet

We have used the following SEQUENCE formula in cell C3 to populate the third row in C3:CP3 with three months of dates (timescale):

=SEQUENCE(1,DAYS(EDATE(C1,3),C1),C1)

How does this formula work?

The EDATE(C1,3) function in the formula returns a date that is three months after the start date in cell C1.

The SEQUENCE function returns sequence numbers in one row and n columns, starting from the date in cell C1. The value of n is determined by the number of days between the date in C1 and EDATE(C1,3).

The following C2 formula converts the populated dates in C3:CP3 to the days of the week:

=ARRAYFORMULA(IF(LEN(C3:3),TEXT(C3:3,"ddd"),))

The following C4 formula in the “Availability” sheet fetches the booking names from the “Reservations” sheet and places them in the 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))))
   ))))
)

Formula Explanation:

The LET function defines five variables:

dt: The dates in row 3 of the “Availability” sheet.
room: The room numbers in column A of the “Availability” sheet.
guest: The guest names in column B of the “Reservations” sheet.
r_room: The room numbers in column A of the “Reservations” sheet.
start: The check-in dates in column E of the “Reservations” sheet.

The MAP function is used to iterate over the dates in dt and the room numbers in room, both in the “Availability” sheet.

For each date (dt) and room number (room), the MAP function uses the FILTER function to filter the guest names in guest (“Reservations”) to only include the names of guests who have booked that room on that date. The result of the MAP function is a two-dimensional array.

The JOIN function joins the filtered guest names into a string, separated by commas (it is part of troubleshooting because there won’t be two names on the same booking date for the same room).

3. Conditional Format (Highlighting) Rules in the Availability Sheet

Highlight rules are essential in creating a visually appealing reservation and booking calendar template in Google Sheets.

We have three conditional formatting rules for the range C4:CP53. To see them, click on cell C4 and go to Format menu > Conditional formatting.

Highlight Rules for the Reservation and Booking Status Calendar Template

The first rule fills the cells corresponding to the booked dates in the timescale (C3:CP3) with an orange color for tentative bookings based on column H in the Reservations sheet.

The second rule replaces the orange color with a light green color for confirmed bookings, also based on column H of the Reservations sheet.

The third rule highlights the night before checkout with a red color (not the checkout date itself). So, if the booking is for one night, the color will be red, not light green or orange.

Here are those formulas (rules used for the range C4:CP53):

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))))
   )
)

This Google Sheets formula is the essence of the reservation and hotel room booking template. It draws the bar for the booked days in the chart area. How?

Formula explanation:

The LET function defines four variables:

from_to: The range of cells in the “Reservations” sheet that contain the check-in and check-out dates for each booking.
booked_room: The range of cells in the “Reservations” sheet that contain the room numbers for each booking.
status: The range of cells in the “Reservations” sheet that contain the statuses of each booking.
room: The room number in A4 of the “Availability” sheet.
dt: The date in C3 of the “Availability” sheet.

The FILTER function filters the from_to range to only include the rows where the room number in the booked_room range matches the value of the room variable and the status of the booking is “Tentative”.

The CHOOSECOLS function separates the filtered from_to range into two parts: start and end dates, named st and en within the LAMBDA in MAP.

The MAP function iterates over the st and en within ISBETWEEN to test if the dt (C$3) falls in that range. The output will be TRUE or FALSE, which the N function converts to 1 or 0, respectively. The SUM function returns the total of it.

If the total is greater than 0, that cell is highlighted.

Since it’s a highlights rule and in room ($A4) the column is fixed and in dt (C$3) the row is fixed, it automatically applies to all rooms and dates in the chart.

Booking Confirmation (Replaces Orange with Light Green)

The same “Orange” rule was copied and modified with the following changes:

The FILTER function was updated to replace the condition “Tentative” with “Confirmed”.

Filter condition in Orange Color Highlighting:

(INDIRECT(status)="Tentative")

Filter condition in Light Green Color Highlighting:

(INDIRECT(status)="Confirmed")

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)))
))

Formula explanation:

The LET function defines five variables:

dt: The date in C3 of the “Availability” sheet.
room: The room number in A4 of the “Availability” sheet.
guest: The range of cells in the “Reservations” sheet that contain the guest names for each booking.
r_room: The range of cells in the “Reservations” sheet that contain the room numbers for each booking.
end: The range of cells in the “Reservations” sheet that contain the check-out dates for each booking.

The FILTER function filters the guest range to only include the rows where the room number in the r_room range matches the value of the room variable and the check-out date in the end range -1 is equal to the given date (dt).

The IFNA function is used to handle the case where there are no guests who have booked the room on the given date. In this case, the IFNA function returns an empty string.

The LEN function returns the length of the string returned by the IFNA function. If the output is greater than 0, the formula highlights the cell.

This rule is applied to the whole chart area. Since the dt (C$3) variable uses an absolute row and the room variable uses an absolute column, it applies to all the ranges in the chart area.

Booking Calendar Template: How to Consider the Check-Out Date (Not Last Night)

Some users may want to highlight the booking start date to the end date (from the check-in date to the check-out date). Above, we considered booked nights.

To make this change, you only need to remove the -1 from the orange, light green, and red highlight rules. You can scroll up to see the formulas where I have highlighted them.

I hope you like this free reservation and booking calendar template! Here are some other popular templates:

156 COMMENTS

Simone October 1, 2024 At 4:13 am

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.

Yohann September 25, 2024 At 5:59 pm

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

- Prashanth KV September 25, 2024 At 10:07 pm
  
  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.
  
Arno June 27, 2024 At 1:50 am

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!

- Prashanth KV June 27, 2024 At 8:45 am
  
  You can do that by displaying booking names in all cells. Please check my answer to the user “Linkz” in the comment section.
  
Hoiman Low May 11, 2024 At 2:45 am

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!

- Prashanth KV May 11, 2024 At 9:07 am
  
  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.
  
Linkz April 15, 2024 At 6:02 pm

Hi, how I can make so that the name of the guest is repeated in every single cell of the green stripe?

- Prashanth KV April 17, 2024 At 8:59 am
  
  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.
  
  - Linkz April 17, 2024 At 1:54 pm
    
    Thank you!
    
Floyd April 11, 2024 At 12:51 pm

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?

- Prashanth KV April 11, 2024 At 1:16 pm
  
  Feel free to share a copy via email or share the URL below. That will help me understand the real scenario.
  
MPR March 27, 2024 At 8:18 pm

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.

- Prashanth KV March 28, 2024 At 12:23 pm
  
  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.
  
  - MPR March 28, 2024 At 4:09 pm
    
    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.
    
    - Prashanth KV March 28, 2024 At 5:54 pm
      
      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.
      
      - MPR March 29, 2024 At 3:02 pm
        
        Thank you very much
        
Brian December 9, 2023 At 1:28 am

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.

- Prashanth KV December 9, 2023 At 9:58 am
  
  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.
  
Daniele Zorrilla December 1, 2023 At 5:39 pm

Any suggestions on how to edit it to manage bookings with hourly slots?

- Prashanth KV December 1, 2023 At 7:51 pm
  
  Sure, I’ll work on a template for managing bookings with hourly slots. I’ll post it as soon as possible. Please stay tuned!
  
- Prashanth KV December 4, 2023 At 8:26 pm
  
  Please check out this Hourly Time Slot Booking Template in Google Sheets.
  
Daniele Zorrilla November 27, 2023 At 9:50 pm

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.

- Prashanth KV November 29, 2023 At 9:39 am
  
  I apologize. Currently, such a template is not available. I will consider this for future development.
  
Prashanth September 28, 2023 At 10:02 pm

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!

Dewa July 13, 2023 At 6:18 am

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.

- Prashanth July 13, 2023 At 10:41 am
  
  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.
  
mjj4884 March 27, 2023 At 10:44 pm

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

- Prashanth March 29, 2023 At 8:27 am
  
  Hi, mjj4884,
  
  This template is not suitable for that.
  
  Thanks.
  
  - Nar May 24, 2023 At 8:34 pm
    
    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.
    
    - Prashanth May 25, 2023 At 7:49 am
      
      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.
      
    - Nar May 25, 2023 At 3:28 pm
      
      After I try to add data to the table, there is an infinite loading.
      
      - Prashanth May 25, 2023 At 8:28 pm
        
        Hi, Nar,
        
        That might be due to the conditional formatting mentioned earlier. I’ve removed that. Please try again.
        
      - Nar May 27, 2023 At 3:07 am
        
        Hi, Prashanth!
        
        Thank you for your work!
        
Nick March 27, 2023 At 12:30 pm

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.

- Prashanth March 27, 2023 At 1:44 pm
  
  Waiting for your response within the Sheet.
  
- Prashanth March 27, 2023 At 5:57 pm
  
  Resolved. Thanks for your support.
  
  - Nick March 28, 2023 At 6:11 pm
    
    Thank you for your time and effort Prashanth
    
Nick March 26, 2023 At 2:46 am

– 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.

- Prashanth March 26, 2023 At 11:04 am
  
  Hi, Nick,
  
  Please share the URL with Editable rights. Also, I haven’t received your email.
  
- Prashanth March 27, 2023 At 9:23 am
  
  Thanks. I got the file and updated the formulas to cover six months of data.
  
Nick March 23, 2023 At 10:25 pm

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.

- Prashanth March 24, 2023 At 11:21 am
  
  Hi, Nick,
  
  Can you share that file?
  
  Share mockup data only.
  
  - Nick March 25, 2023 At 3:31 pm
    
    Hi Prashanth,
    
    Did you get the file?
    
    - Prashanth March 25, 2023 At 9:50 pm
      
      I didn’t. You can share it (URL) below. I won’t publish that comment.
      
Nick March 18, 2023 At 4:07 pm

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?

- Prashanth March 19, 2023 At 6:59 am
  
  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.
  
  - Nick March 19, 2023 At 2:25 pm
    
    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
    
    - Prashanth March 20, 2023 At 4:26 am
      
      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)-1 formula in Availability!G1.
      3. Replace Filtered!$D$3:$CY part of the Availability!C4 formula with Filtered!$D$3:$GU.
      
      Please try it and let me know.
      
Luca February 17, 2023 At 10:51 am

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.

- Prashanth February 21, 2023 At 9:11 am
  
  Hi, Luca,
  
  Sorry! I’m not clear 🙁
  
Ariel February 7, 2023 At 5:28 pm

Great, I understand.

“You can use Data (menu) > Data Validation to avoid accidentally entering values.”

How do I do this exactly?

- Prashanth February 8, 2023 At 10:11 am
  
  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:C13
  5. 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.
  
Ariel February 3, 2023 At 2:36 am

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!

- Prashanth February 3, 2023 At 11:45 am
  
  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.
  
Ariel February 1, 2023 At 4:30 pm

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.-

- Prashanth February 2, 2023 At 1:18 pm
  
  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.
  
Ariel January 30, 2023 At 3:24 pm

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.

- Prashanth January 30, 2023 At 5:45 pm
  
  Hi, Ariel,
  
  Please send it but only with a few records for fast-loading the file.
  
  Also, replace real names with dummy names.
  
Ariel January 25, 2023 At 4:54 pm

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!!

- Prashanth January 27, 2023 At 10:58 am
  
  Hi, Ariel,
  
  Please go through my reply to Kerim.
  
Kerim January 12, 2023 At 4:25 pm

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 —

- Prashanth January 13, 2023 At 6:29 pm
  
  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.
  
anas shahbander December 27, 2022 At 2:51 am

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.

Abu December 15, 2022 At 6:46 am

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.

- Prashanth December 15, 2022 At 11:01 am
  
  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.
  
Mohamed December 9, 2022 At 11:02 am

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😃

- Prashanth December 9, 2022 At 4:14 pm
  
  Hi, Mohamed,
  
  Good to hear that 🙂
  
Nandhini November 16, 2022 At 8:17 am

What to do when a customer does not give a checkout date? how to display ?

Prashanth October 1, 2022 At 5:57 pm

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.

- Anita November 9, 2022 At 6:42 pm
  
  Sounds great, Prashanth. What do we need to do to update our existing sheets?
  
  - Prashanth November 10, 2022 At 11:27 am
    
    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.
    
Hasse September 2, 2022 At 6:14 pm

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!!

Anita August 8, 2022 At 12:42 pm

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 —

- Prashanth August 9, 2022 At 4:36 pm
  
  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).
  
Natasha August 4, 2022 At 9:17 pm

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!

- Prashanth August 5, 2022 At 6:36 pm
  
  Hi, Natasha,
  
  Could you copy that sheet and share its URL with me in your comment/reply below?
  
  - Natasha August 6, 2022 At 12:28 am
    
    — 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.
    
    - Prashanth August 6, 2022 At 11:14 am
      
      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$3 with "^"&$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.
      
      - Natasha August 6, 2022 At 9:58 pm
        
        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?
        
        
        Prashanth August 8, 2022 At 10:32 am
        
        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.
        
Laura P June 29, 2022 At 9:41 pm

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!

- Prashanth July 1, 2022 At 10:49 am
  
  Hi, Laura P,
  
  Sorry! It won’t work.
  
Patricia Swenson May 27, 2022 At 4:01 am

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!

- Prashanth May 27, 2022 At 11:11 am
  
  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.
  
Nikki M Calles May 4, 2022 At 6:19 pm

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!

- Prashanth May 8, 2022 At 3:48 pm
  
  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.
  
  - Nikki M Calles May 9, 2022 At 6:11 am
    
    Thank you!
    
Myson Mark May 3, 2022 At 9:33 am

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.

- Prashanth May 4, 2022 At 10:11 am
  
  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.
  
  - Myson Mark May 4, 2022 At 12:10 pm
    
    Thanks a lot Prashanth 🙂
    
Myson Mark May 2, 2022 At 6:28 pm

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?

- Prashanth May 3, 2022 At 9:13 am
  
  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.
  
Anita April 30, 2022 At 5:19 pm

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.

- Prashanth May 1, 2022 At 10:05 am
  
  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.
  
  - Anita May 13, 2022 At 9:53 pm
    
    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.
    
    - Prashanth May 14, 2022 At 8:09 am
      
      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.
      
Melissa April 21, 2022 At 6:52 pm

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?

- Prashanth April 22, 2022 At 2:34 pm
  
  Hi, Melissa,
  
  Go to the File menu and click Version history and revert to a previous version.
  
  - Melissa April 25, 2022 At 1:05 am
    
    Will I lose the information I have in the file?
    
    - Prashanth April 25, 2022 At 9:02 am
      
      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.
      
Francisco April 5, 2022 At 1:23 am

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.

- Prashanth April 6, 2022 At 11:08 am
  
  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.
  
  - Francisco April 7, 2022 At 3:42 pm
    
    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.
    
    - Prashanth April 10, 2022 At 12:38 pm
      
      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.
      
Beans February 24, 2022 At 12:25 am

What if you wanted to change the Check-in/Check-out dates to times?

- Prashanth February 24, 2022 At 8:10 am
  
  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.
  
Joseph February 23, 2022 At 1:27 pm

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.

- Prashanth February 24, 2022 At 8:07 am
  
  Hi, Joseph,
  
  Welcome!
  
  That doesn’t work. I already have formulas in those cells for highlighting.
  
Theresa January 9, 2022 At 8:29 am

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.

- Prashanth January 10, 2022 At 8:28 am
  
  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).
  
Angel December 3, 2021 At 1:41 pm

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,

- Prashanth December 4, 2021 At 6:26 am
  
  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.
  
Oliver December 1, 2021 At 3:53 pm

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!

- Prashanth December 2, 2021 At 8:30 am
  
  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")
  
Robin October 4, 2021 At 7:40 pm

Hi,

Thanks for the share, any update on the double booking fix?

Regards

- Prashanth October 5, 2021 At 12:15 pm
  
  Hi, Robin,
  
  I have elaborated on all the possible fixes in answers to previous user comments.
  
  You may please check that.
  
anubhav agrawal August 26, 2021 At 8:31 pm

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?

- Prashanth August 27, 2021 At 10:28 am
  
  Hi, Anubhav Agrawal,
  
  Please go through the comments by user Sandy and my answers to them.
  
Sandy March 13, 2021 At 8:23 am

Thank you very much, Prashanth, much appreciated.

Sandy March 11, 2021 At 10:23 am

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

- Prashanth March 13, 2021 At 6:55 am
  
  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.
  
Sandy March 10, 2021 At 1:51 am

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

- Prashanth March 10, 2021 At 9:44 am
  
  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.
  
Sandy March 9, 2021 At 8:28 am

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.

- Prashanth March 9, 2021 At 11:13 am
  
  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.
  
Sandy March 7, 2021 At 12:43 pm

Awesome, thank you.

Sandy March 5, 2021 At 10:23 am

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

- Prashanth March 6, 2021 At 9:45 am
  
  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.
  
Sandy March 2, 2021 At 5:56 am

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

- Prashanth March 2, 2021 At 12:33 pm
  
  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”.
  
Sandy McCleary March 2, 2021 At 4:13 am

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?

- Prashanth March 2, 2021 At 10:43 am
  
  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.
  
  - Sandy March 5, 2021 At 9:26 am
    
    Thank you, Prashanth.
    
Michelle Kennard October 29, 2020 At 6:27 am

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

Nikki Smith August 3, 2020 At 8:44 pm

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?

- Prashanth August 4, 2020 At 9:12 am
  
  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.
  
Adam July 25, 2020 At 2:59 am

Thanks for making this, what are the numbers that show in the filtered tab?

- Prashanth July 25, 2020 At 9:00 am
  
  Hi, Adam,
  
  In the hidden (grouped column) right?
  
  That are dates expanded formatted as values.
  
  - Adam July 25, 2020 At 5:44 pm
    
    Thanks for the speedy reply. Yeah the hidden grouped column, do they serve a purpose for the other tabs?
    
    - Prashanth July 26, 2020 At 9:25 am
      
      Hi, Adam,
      
      It’s actually expanded check-in and check-out days and used for highlighting.
      
Veronica June 29, 2020 At 8:45 pm

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

- Prashanth June 30, 2020 At 3:19 pm
  
  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.
  
Emilia May 13, 2020 At 10:21 pm

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.

philip April 19, 2020 At 9:41 pm

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

- Prashanth April 21, 2020 At 10:43 am
  
  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.
  
Georgia March 16, 2020 At 3:06 pm

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

- Prashanth March 17, 2020 At 5:56 am
  
  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)-1
  
  Wait 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.
  
  - Jenee February 12, 2022 At 5:01 am
    
    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?
    
    - Prashanth February 12, 2022 At 9:18 am
      
      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.
      
Lucas January 7, 2020 At 7:56 pm

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. 🙂

- Pepe June 4, 2021 At 12:11 am
  
  Any update on the double booking?
  
camila December 11, 2019 At 1:17 pm

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.

- Prashanth December 11, 2019 At 3:10 pm
  
  Thanks for letting me know about the ‘issue’. I’ll check it and update you.
  
Joel Brodard December 2, 2019 At 10:01 am

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!

- Prashanth December 2, 2019 At 11:04 am
  
  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,