How can I get a handle on Asset/Room availability with SmartSheet?

I manage a research program with 11 rooms (which can essentially be viewed as an asset) available for contract use. I'm looking for a better way to determine when I have rooms available for use. I've attached the smartsheet that I use to record the current room usage and then a report that I generate to scan each room for a window of available dates to use for booking new contracts. For example, someone comes to me and says they need to run a 30 day study, when's the earliest we could book this. Then I look at the report and scan each room to see if there is a window of time that works for the contract as well as the size of the room to accommodate the research proposal. I find the soonest available room and book them for that timeframe. Is there a more efficient way to do this? A formula that I could use to see how many rooms are available across time? The room usage SS has the contract name, Room number, Start/End Date. I am definitely a newbie to the world of smartsheet!


  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/01/21

    Hi @MHente

    I would potentially suggest using the Calendar View instead of Gantt, although both work!

    Personally, I would colour-code each room's Task Bar using conditional formatting so it's easy to see in a Calendar, like so:

    Then I would have three filters, one for each of the Capacity sizes, so when someone identifies their capacity I can quickly filter down to the possible rooms:

    Essentially this would be the same as what you're doing though, where you scan to see the available dates.

    To make sure you don't add an overlapping date, I would add in a Flag Column and use this formula to check for duplicates:

    =IF(COUNTIFS([Room Number]:[Room Number], [Room Number]@row, Start:Start, OR(@cell <= Start@row, @cell <= Finish@row), Finish:Finish, OR(@cell >= Start@row, @cell >= Finish@row)) > 1, 1, 0)

    It wouldn't let you know what's currently available, but it will flag whatever row in your sheet would be blocking your current room booking.

    Let me know if any of this helped!