HomeGoogle DocsSpreadsheetReservation and Booking Status Calendar Template in Google Sheets

Reservation and Booking Status Calendar Template in Google Sheets

Published on

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:

  1. The template can show three months of booking data based on a provided start date. You can choose different start dates.
  2. I have added rows for 50 rooms, but you can add more rows if you have more rooms.
  3. 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.)
  4. 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.

Input area in the Availability sheet

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.

Input area in the Reservations sheet

You are required to enter or select the following data:

  1. Pick room numbers in A3:A (The drop-downs will be populated with all your room numbers in Availability!A4:A53).
  2. Enter the check-in date in E3:E and the check-out date in F3:F.
  3. Enter the booking name in B3:B.
  4. 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:

  1. Calendar View in Google Sheets (Custom Template)
  2. Array Formula to Split Group Expenses in Google Sheets (Template)
  3. Create a Habit Tracker in Google Sheets: Step-by-Step Guide (Template)
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

144 COMMENTS

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

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

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

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

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

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

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

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

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

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

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

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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here