Formula to book 30 minute time slots based on event duration

KT_H

I am working on a room booking solution that would utilize calendar app to show availability for a particular room.

The current workflow is:

1. Request a room by completing a form (indicate start time and end time of booking request)
2. Request is approved or declined by an administrator
3. Once approved, the times booked will show as "BOOKED" on the calendar app.

I have written a formula that searches for the start time, but I am not sure then how to book out each corresponding 30 minute time slot that are included in the approved booking.

An Example of a booking request below:

An example of the room availability sheet:

Happy to share any additional details that may help clarify.

jmyzk_cloudsmart_jp

Hi @KT_H

Here is my solution. (The activity log shows that developing this took more than three hours.😅)

https://app.smartsheet.com/b/publish?EQBCT=986dc04fc50947b98c1191c1b5d39c49

Booking request sheet

1. I converted the AM/PM format to the 24-hour format to simplify the calculation.
   • [Start 24]=IF(ISTEXT([Start Time]@row), TIME([Start Time]@row, 1))
2. Then, I converted [Start 24] to a 30-minute slot number, 1 to 17.
   • [S #] =(VALUE(LEFT([Start 24]@row, 2)) - 9) * 2 + VALUE(RIGHT([Start 24]@row, 2)) / 30 + 1

• Finally, I created a multiple dropdown list column, "Slots", with the [S #] and [E #] values.
  • =[S #]@row + IF([S #]@row + 1 <= +[E #]@row, CHAR(10) + ([S #]@row + 1)) + IF([S #]@row + 2 <= +[E #]@row, CHAR(10) + ([S #]@row + 2)) << 3 to 16, omitted >>+ 17 <= +[E #]@row, CHAR(10) + ([S #]@row + 17))

For readability, the third formula has this structure. (The whole formula is in the Sheet Summary of the request sheet.)

=[S #]@row

+ IF([S #]@row + 1 <= +[E #]@row, CHAR(10) + ([S #]@row + 1))

+ IF([S #]@row + 2 <= +[E #]@row, CHAR(10) + ([S #]@row + 2))

<<3 to 16, omitted>>

+ IF([S #]@row + 17 <= +[E #]@row, CHAR(10) + ([S #]@row + 17))

https://app.smartsheet.com/b/publish?EQBCT=d1e84954c597425aabb5b22fe6190809

Room availability sheet

1. The room availability sheet gets the [Slots] values of the above sheet for each [Time Slot] row of the data with the following formula.
   • [Day Slots] =JOIN(COLLECT({ALL}, {Date}, [Parent Date]@row), CHAR(10))
     • ({ALL} refers to the [Slots] range of the booking request sheet.
     • {Date} refers to the [Date] range of the same sheet.

• The [T #] is the same 30-minute slot number as the above sheet. (Same form of formula.)
• The following formula determines [Not Availale] by comparing [T #] and [Day Slots] values with the HAS function.
  • [Not Available] =IF(HAS([Day Slots]@row, [T #]@row), 1, 0)

If I hide the gray helper columns, the image on the right shows how the sheet looks.

Booking Request Form

You can test how those formulas work by inputting a new entry with the following form. (To avoid abuse, I request Smartsheet login.)

https://app.smartsheet.com/b/form/e4fe316871114d0d90913b0d7180e985

Request for a copy of this solution

If you need a copy of this solution, please get in touch with me at info@cloudsmart.jp

jmyzk_cloudsmart_jp

Hi @KT_H

Here is my solution. (The activity log shows that developing this took more than three hours.😅)

https://app.smartsheet.com/b/publish?EQBCT=986dc04fc50947b98c1191c1b5d39c49

Booking request sheet

1. I converted the AM/PM format to the 24-hour format to simplify the calculation.
   • [Start 24]=IF(ISTEXT([Start Time]@row), TIME([Start Time]@row, 1))
2. Then, I converted [Start 24] to a 30-minute slot number, 1 to 17.
   • [S #] =(VALUE(LEFT([Start 24]@row, 2)) - 9) * 2 + VALUE(RIGHT([Start 24]@row, 2)) / 30 + 1

• Finally, I created a multiple dropdown list column, "Slots", with the [S #] and [E #] values.
  • =[S #]@row + IF([S #]@row + 1 <= +[E #]@row, CHAR(10) + ([S #]@row + 1)) + IF([S #]@row + 2 <= +[E #]@row, CHAR(10) + ([S #]@row + 2)) << 3 to 16, omitted >>+ 17 <= +[E #]@row, CHAR(10) + ([S #]@row + 17))

For readability, the third formula has this structure. (The whole formula is in the Sheet Summary of the request sheet.)

=[S #]@row

+ IF([S #]@row + 1 <= +[E #]@row, CHAR(10) + ([S #]@row + 1))

+ IF([S #]@row + 2 <= +[E #]@row, CHAR(10) + ([S #]@row + 2))

<<3 to 16, omitted>>

+ IF([S #]@row + 17 <= +[E #]@row, CHAR(10) + ([S #]@row + 17))

https://app.smartsheet.com/b/publish?EQBCT=d1e84954c597425aabb5b22fe6190809

Room availability sheet

1. The room availability sheet gets the [Slots] values of the above sheet for each [Time Slot] row of the data with the following formula.
   • [Day Slots] =JOIN(COLLECT({ALL}, {Date}, [Parent Date]@row), CHAR(10))
     • ({ALL} refers to the [Slots] range of the booking request sheet.
     • {Date} refers to the [Date] range of the same sheet.

• The [T #] is the same 30-minute slot number as the above sheet. (Same form of formula.)
• The following formula determines [Not Availale] by comparing [T #] and [Day Slots] values with the HAS function.
  • [Not Available] =IF(HAS([Day Slots]@row, [T #]@row), 1, 0)

If I hide the gray helper columns, the image on the right shows how the sheet looks.

Booking Request Form

You can test how those formulas work by inputting a new entry with the following form. (To avoid abuse, I request Smartsheet login.)

https://app.smartsheet.com/b/form/e4fe316871114d0d90913b0d7180e985

Request for a copy of this solution

If you need a copy of this solution, please get in touch with me at info@cloudsmart.jp

KT_H

@jmyzk_cloudsmart_jp wow! Thank you so much for your efforts here. You definitely went above and beyond. I will test this out and let you know how it goes.

RDRGSJ00

I was working on a similar project and was stuck on this for a couple of days. This was a lifesaver. Thank you @KT_H you're awesome.