Our free Excel template for hourly time slot booking helps you view 24/7 booking status on a bar chart based on a start date.

The template uses Lambda functions, so you can open the file in Excel in Microsoft 365. Other Excel versions do not support it.

You can use our free Excel template for hourly time slot booking for various purposes such as medical appointments, job interviews, gym classes, meeting rooms, salon and spa services, library study rooms, sports facilities, car rentals, and more.

If you prefer daily booking, please check out our post titled: Excel: Hotel Room Availability and Booking Template (Free). You can fine-tune this template to suit your needs.

You can find the download link to the template and the usage instructions below. Before that, here are the salient features of this free Excel template:

• Displays 24/7 booking and availability status.
• Shows booking persons’ names in each time slot.
• Allows you to choose a start date to view the availability status of time slots in a Gantt chart-like template for one week starting from that date.
• At a time, the preformatted Gantt chart view displays up to 50 items, totaling 8,400 time slots (50 * 7 * 24). You can add or remove items as needed. Fewer items improve performance.
• Restricts the Gantt chart view to confirmed bookings, tentative bookings, or all booking types.
• Alerts for duplicate time slot bookings.
• Ensure ease of use by requiring inputs only for the item (the booking subject), the person who booked, the start time, the end time (both in timestamp format), and the type of booking (tentative or confirmed).

Get Your Free Template

Visual Walkthrough: Excel’s Hourly Time Slot Booking

There are two worksheets in the workbook (free template): ‘Status’ (for viewing the availability of time slots in a Gantt chart calendar view) and ‘Booking’ (to enter the booking). Here is how to use them.

Data Entry in the Time Slot Booking Worksheet

In the ‘Booking’ worksheet of the hourly timeslot booking template, we use a table so that the formatting copies automatically when we add new records.

The table currently contains sample data. You can delete the records from row #4 onwards. Overwrite the data in row #3 as follows:

• Cell A3 contains a drop-down. It will pre-populate data from the ‘Status’ worksheet A4:A53. You should select the item that’s being booked from the drop-down.
  You should first enter the ‘Booking Purpose’, the things being booked, in the ‘Status’ worksheet in the range A4:A53.
• In cell B3, enter the person who booked the item. I prefer a shorter name or ID as this will appear on the time slots in the ‘Status’ worksheet. If the name is longer, enter it in a different column, for example, in cell H3, and enter the shorter one in cell B3.
• In cells C3 and D3, enter the booking start datetime and end datetime, respectively.
• You can skip cell E3 as it contains the following formula that returns the duration:

=[@End]-[@Start]

• In cell F3, select whether the booking is “Tentative” or “Confirmed” from the drop-down list.
• Cell G3 contains the following formula, which helps highlight duplicate entries:

=LET(dt,SEQUENCE([@End]-[@Start]+1,1,[@Start]), bkd, FILTER(Table1[[Start]:[End]],([Booking Purpose]=[@[Booking Purpose]])*(ROW([Booking Purpose])<>ROW([@[Booking Purpose]]))),IFERROR( SUM(MAP(CHOOSECOLS(bkd, 1), CHOOSECOLS(bkd, 2), LAMBDA(x,y, SUM((dt>x)*(dt<y))))),0)>0)

The formula returns TRUE or FALSE, where TRUE indicates a duplicate entry. When booking, if any rows are highlighted in red, you should correct the timestamp entries in the last booked item, not all highlighted rows. Here is the highlight rule in use: =$G3=TRUE

Should I copy the formulas down?

No. The formulas will expand on their own because we used structured references.

Data Entry in the Status View Worksheet

In cells A4:A53 in the ‘Status’ worksheet, enter all the items that you want to make available for hourly time slot booking, such as car registration numbers for a car rental service, doctors’ names for hospital or clinic appointments, etc.

This represents the only required data input in the ‘Status’ worksheet for our free hourly time slot booking template in Excel.

There are three formulas in this worksheet:

• In cell C3, we use the following formula to horizontally populate the 24/7 timescales based on the date entered in cell G1:

=G1+SEQUENCE(1, 7*24, 0)/24

• The following formula in cell C2 returns dates when date changes in row #3:

=IF(MOD(C3:FN3, 1)=TIME(0, 0, 0), INT(C3:FN3),"")

• All the formulas in the hourly time slot booking template are important. But the following formula in cell C4 is most important as it populates the booked persons’ names within the grid. This facilitates highlighting those cells.

=LET(dt,C3:FN3,room,A4:A53,guest,Table1[Booked By],r_room,Table1[Booking Purpose],
start,Table1[Start],end,Table1[End],status,Table1[Status],
DROP(REDUCE("",room,LAMBDA(a,v,VSTACK(a,MAP(dt,LAMBDA(c,IFERROR(FILTER(guest,IF(O1="All", 1, status=O1)*(r_room=v)*(c>=start)*(c<end)),"")))))),1))

Highlight Rules:

The ‘Status’ worksheet in the hourly time slot booking template in Excel contains a couple of highlight rules. Not all of them are necessary, as some are for improving the aesthetic view of the worksheet.

Here are the necessary ones:

1. =AND(C4<>"", $O$1="All") – highlights “All” booking.
2. =AND(C4<>"", $O$1="Confirmed") – highlights “Confirmed” booking.
3. =AND(C4<>"", $O$1="Tentative") – highlights “Tentative” booking.

We applied the above rules to the range C4:FN53.

Understanding the Status View: Excel Template for Hourly Time Slot Booking

To view the time slots from 01/04/2024 to 07/04/2024, enter 01/04/2024 in cell G1.

You can choose one of the three calendar views in cell O1: All, Confirmed, or Tentative.

• All: To display the bars for all types of bookings, whether confirmed or tentative.
• Confirmed: To display confirmed booking bars only.
• Tentative: To display tentative booking bars only.

To check whether an item is available for booking, I suggest selecting “All” in cell O1.

In the ‘Status’ worksheet, each cell in the grid represents 1 hour.

For example, if Lisa booked the Rowing Machine for 4 hours from 01-04-2024 14:00:00 to 01-04-2024 18:00:00, the cells under 14:00, 15:00, 16:00, and 17:00 will be highlighted. Also, her name will appear in these cells.

Another booking for the same machine from 01-04-2024 18:00:00 to 01-04-2024 20:00:00, cells under 18:00 and 19:00 will be highlighted, and so will the name.

All the cells with a blue-filled color or cells that do not contain a name represent empty time slots, available for booking.

Conclusion

I hope you find our free Hourly Time Slot Booking Template in Excel useful. Additionally, you can utilize it effectively with the examples above.

Feel free to reach out if you have any questions or issues while starting to use it. We’re here to help you get the most out of it! While we are not currently accepting feature requests, we value your feedback and will consider them for future development.

Please refer to the “©” worksheet in the template to understand our fair-use policy.