Jul 16 2024

09:12 AM

As an admin of a booking calendar, I often face the challenge of managing multiple colleagues’ schedules to ensure that they are available for bookings at specific times. In this blog post, I will discuss how to only allow the desired times to be shown as available and display staff members as unavailable if they have other meetings or appointments during those times.

The Challenge: Allowing Only Specific Times to Be Shown As Available

One of the common issues we face is that some staff members have busy calendars with multiple meetings and appointments throughout the day. When a booking is made, we want to ensure that only the specific times we have designated as available are shown as such, rather than displaying any other available time during the day.

For instance, if we have designated the following times as available for booking:

* 10:00 AM – 11:30 AM

* 13:00 PM – 14:30 PM

* 16:00 PM – 17:30 PM

We want to ensure that only these times are shown as available, and not any other time during the day. Additionally, if a staff member has another meeting or appointment during one of these designated times, we want to display them as unavailable instead of showing a random time during the day.

The Solution: Using Conditional Formatting in Google Calendar

To achieve this, we can use conditional formatting in Google Calendar to highlight only the specific times we have designated as available. Here’s how to do it:

1. Create a new calendar event for each of the designated times you want to show as available. For example, create events for 10:00 AM, 13:00 PM, and so on.

2. Set the duration of each event to 90 minutes (or any other duration you prefer).

3. Use conditional formatting to highlight only these specific times in your colleagues’ calendars. To do this, follow these steps:

a. Open the calendar of the staff member you want to apply the conditional formatting to.

b. Click on the “Settings” icon (represented by a gear icon) in the bottom right corner of the calendar.

c. Select “Conditional Formatting” from the drop-down menu.

d. In the “Format cells if” dropdown, select “Custom formula is”.

e. Enter the following formula: =CALENDAR_EVENT_START_TIME=CURRENT_TIME

f. Replace “CALENDAR_EVENT_START_TIME” with the name of the event start time column in your calendar (usually “start_time”).

g. Replace “CURRENT_TIME” with the name of the current time column in your calendar (usually “current_time”).

h. Click “Done” to save the formatting rule.

4. Repeat steps 2-3 for each designated time you want to show as available.

Now, when a staff member has another meeting or appointment during one of the designated times, Google Calendar will display them as unavailable instead of showing a random time during the day. Only the specific times we have designated as available will be shown as such, and any other times will be displayed as unavailable.

Tips and Tricks: Advanced Conditional Formatting Techniques

While the above solution should cover most use cases, there may be situations where you need more advanced conditional formatting techniques. Here are some tips and tricks to help you achieve more complex formatting rules:

1. Use multiple conditions: You can use multiple conditions in your custom formula to create more complex formatting rules. For example, you can check if an event starts within a certain time frame or if it overlaps with another event.

2. Use named ranges: Instead of hardcoding column names in your formula, you can use named ranges to make your formula more flexible and easier to maintain.

3. Use the “OR” operator: You can use the “OR” operator to check for multiple conditions in a single formula. For example, you can check if an event starts within a certain time frame or if it overlaps with another event.

4. Use the “AND” operator: You can use the “AND” operator to check for multiple conditions in a single formula. This can be useful when you want to display an event as available only if all conditions are met.

5. Use the “IF” function: Instead of using a custom formula, you can use the “IF” function to simplify your conditional formatting rules. For example, you can use “IF(start_time=current_time, ‘Available’, ‘Unavailable’)” to display an event as available if its start time matches the current time.

Conclusion

In this blog post, we discussed how to only allow specific times to be shown as available in a booking calendar and display staff members as unavailable if they have other meetings or appointments during those times. We also covered some tips and tricks for advanced conditional formatting techniques to help you achieve more complex formatting rules. By using these techniques, you can create a more efficient and organized booking system that meets the needs of your colleagues and clients.