Meeting Room booking using SS Calendar App

Joseph Aloysias

Hi All,

I'm in the process of setting up a meeting room booking system where I display date/time availability and different status indicators (confirmed, tentative, waitlist).

I'm currently using two sheets: one to collect meeting information via a form and the other to manage meeting slots on the calendar. I've successfully blocked 30-minute slots using a formula that utilizes the INDEX and COLLECT functions to match the date, time, and meeting room.

=IFERROR(IF(INDEX(COLLECT({Meeting Intake - Meeting Room}, {Meeting Intake - Meeting Room}, @cell = [Meeting Room]@row, {Meeting Intake Date}, Date@row, {Meeting Intake Meeting Slot}, [Meeting Slots]@row), 1) = [Meeting Room]@row, "Booked", "Available"), "Available")

Now, I'm looking for a way to block the calendar for longer duration, such as from 8:00 am to 11:00 am. How can I achieve this with my formula? or any other workaround?

Paul Newcome

There are some formulas here that will help you convert the time into numeric data:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

There are also quite a few threads here in the Community that provide solutions for finding overlap.

Paul Newcome

Happy to help. 👍️

Vivien Chong

I would also like to know the formula to achieve this!

Joseph Aloysias

@Paul Newcome @Genevieve P. any suggestions on the above query?

Paul Newcome

You will need to convert your times into usable numbers. Then you would be able to use arguments such as less than, equal to, and / or greater than to use overlap logic.

Joseph Aloysias

@Paul Newcome Thank you for your reply, appreciate it. can you please provide the workflow and formula?

Paul Newcome

There are some formulas here that will help you convert the time into numeric data:

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

There are also quite a few threads here in the Community that provide solutions for finding overlap.

Joseph Aloysias

This is fantastic! Thank you very much, @Paul Newcome , for your time and assistance!

Paul Newcome

Happy to help. 👍️

Joseph Aloysias

Hi @Paul Newcome, I tried using the formula but I got #Invalid Value error, Could you please help fixing the formula?

=IF(COUNTIFS([Meeting Room]:[Meeting Room], [Meeting Room]@row, Date:Date, Date@row, Finish:Finish, @cell >= Start@row, Start:Start, @cell <= Finish@row) > 1, 1)

Paul Newcome

Do you have that error in even just one cell of any of the columns being referenced?