Meeting Room booking using SS Calendar App
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?
Best Answers
-
There are some formulas here that will help you convert the time into numeric data:
There are also quite a few threads here in the Community that provide solutions for finding overlap.
-
Happy to help. 👍️
Answers
-
I would also like to know the formula to achieve this!
-
@Paul Newcome @Genevieve P. any suggestions on the above query?
-
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.
-
@Paul Newcome Thank you for your reply, appreciate it. can you please provide the workflow and formula?
-
There are some formulas here that will help you convert the time into numeric data:
There are also quite a few threads here in the Community that provide solutions for finding overlap.
-
This is fantastic! Thank you very much, @Paul Newcome , for your time and assistance!
-
Happy to help. 👍️
-
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)
-
Do you have that error in even just one cell of any of the columns being referenced?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!