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

MHente

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!

Research space useage.pdf

research space report view.jpg

Genevieve P.

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!

Cheers,

Genevieve