Formula to book 30 minute time slots based on event duration

Options

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.

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/03/24 Answer ✓
    Options

    Hi @KT_H

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

    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))

    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.)

    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

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/03/24 Answer ✓
    Options

    Hi @KT_H

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

    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))

    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.)

    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
    KT_H ✭✭✭
    Options

    @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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!