Rental Availability Formula
I am trying to make a formula in smartsheets where I can check availability. We have a current schedule that consists of events where a trailer is booked out between dates. I have formula that will give me the daily count of how many trailers are booked on a particular day.
=11 - (COUNTIFS([Restroom Trailer]:[Restroom Trailer], "2 Stall Restroom Trailer", [Task Type]:[Task Type], "Long Term Event", [Event (or Job) Start Date]:[Event (or Job) Start Date], @cell <= [Event (or Job) Start Date]@row, [Event (or job) End Date]:[Event (or job) End Date], @cell >= [Event (or Job) Start Date]@row)) - (COUNTIFS([Restroom Trailer]:[Restroom Trailer], "2 Stall Restroom Trailer", [Event (or Job) Start Date]:[Event (or Job) Start Date], AND(@cell >= [Event (or Job) Start Date]@row, @cell <= [Event (or Job) Start Date]@row), [Task Type]:[Task Type], "Event"))
What I'm trying to figure out is how to plug in an example date range to check availability on particular trailer sizes. Viewing this as a gantt does not work well. I have created a report to try and view this with no success either. Using filters has not quite given me what I need either. Although a combination of these could work in some way.
Basically I want to input a range of two dates and have it spit out the number of trailers available during those two dates based on what is already booked.
Does anyone have a formula that calculates availability based off existing events booked?
Best Answer
-
Paul, I was able to figure it out:
=17 - (MAX(COLLECT(CHILDREN([Event/Customer]130), CHILDREN([Event (or Job) Start Date]130), @cell >= [Event (or Job) Start Date]130, CHILDREN([Event (or Job) Start Date]130), @cell <= [Event (or job) End Date]130))) + " 2 Stalls Available"
Thank you for all your help!
Answers
-
I'll have to get back to you on the formula to pull the count for what is available (my day is almost over), but I did have a question about your existing formula as well (bold portion)...
=11 - (COUNTIFS([Restroom Trailer]:[Restroom Trailer], "2 Stall Restroom Trailer", [Task Type]:[Task Type], "Long Term Event", [Event (or Job) Start Date]:[Event (or Job) Start Date], @cell <= [Event (or Job) Start Date]@row, [Event (or job) End Date]:[Event (or job) End Date], @cell >= [Event (or Job) Start Date]@row)) - (COUNTIFS([Restroom Trailer]:[Restroom Trailer], "2 Stall Restroom Trailer", [Event (or Job) Start Date]:[Event (or Job) Start Date], AND(@cell >= [Event (or Job) Start Date]@row, @cell <= [Event (or Job) Start Date]@row), [Task Type]:[Task Type], "Event"))
What is the purpose of the bold portion? You include a date range and then say to count any dates within that range that are less than, equal to, AND greater than the [Event (or Job) Start Date]@row. The only dates that this should be pulling into the count would be the dates that are equal to the start date.
-
Thanks Paul. We have two different types of rentals, an event based rental (which is generally a rental that only lasts for 3 days or less) and a long term based rental (a rental going longer than 3 days, these generally go for months at a time). I can redo our schedule so that our events fall in a range instead of have two different criterias to search through.
-
So where exactly would you want to plug in your date range to get a count of what is available?
-
I am trying to plug the 'dates of a potential job' into the same column above the other dates. And then if possible I would like another cell in that row say how how many trailers are available during that range of dates. It's like I'm looking for the minimum number available over those dates. For example, if I am looking to have someone rent a two stall trailer between the dates of 4/1/21 to 8/1/21. Overall we have 17 two stall trailers. Over the course of those dates we have 11 long term events where a trailer is rented at some point over that time. We also have a weekend (amongst many weekends) where the biggest current weekend we have 6 additional 2 stall trailers rented for events. So over that course of time I would have 1 available trailer based on 16 being used at at least one point in time over the dates 4/1/21 to 8/1/21.
So it's like I'm looking for the minimum number of available trailers over the projected timeframe. I uploaded a pdf copy of the top portion of an example schedule. I included a gantt view which for the time being we can just count how many trailers we have and know what we have available and what we don't. For the moment I can have our calendar filter out all our other tasks and list how many of each kind of trailer we have for every day. This works overall for now, but won't be sustainable as we scale. If any of this makes sense.
-
Ok. Try something like this...
=COUNT(CHILDREN([Even/Customer]181) - COUNTIFS(CHILDREN([Event (or job) Start Date]181), @cell >= [Event (or job) End Date]@row, CHILDREN([Event (or job) End Date]181), @cell >= [Event (or job) Start Date]@row)
-
That puts me on a better path as I didn't realize you could use the CHILDREN function from a Primary columns children that were indented in. But this isn't quite getting me to what I am asking. I also apologize as this is a confusing request and I don't know if I'm phrasing it well enough for what I mean to say. It's like I want the count of 2 stall trailers that are already in the column falling between the two dates (mainly events). But I also want it to count the number of those 'long term events' where the long term event start date and end date falls between the availability check date as well. Then I want to take the amount of 2 stall trailers we have and subtract them from that count.
To make sure I understand this, you are getting a Count for the number of rows under Event/Customer (primary column) minus the Count if the number of those rows where the date falls between the two dates put in the row? What this seems to be counting is the number of events listed on the whole on the schedule minus the number of those events that fall between the two dates....possibly.
That might work if I put a filter in to filter out indented tasks underneath the event itself (like delivery and pick up date events). And then I would need to take the amount of trailers we have minus that count number the formula would give me. We also have different types of trailers where I am trying to get the number of each stall remaining. But if we can figure out how to get it from one set of stalls then I can figure out the others.
-
How are you identifying stalls?
You are correct on the intended logic of the formula. Count how many stalls you have then subtract how many fall within those date ranges.
-
I have another column labeled "Restroom Trailer" where we assign if it's a 2 stall Restroom Trailer, 4 Stall Restroom Trailer, 8 Stall Restroom Trailer, etc. I think I deleted the information under there on my pdf upload so I didn't put any customer details out there.
-
Are you able to provide screenshots that show all pertinent columns? Sensitive/confidential data can be replaced with "dummy data" as needed.
-
I believe the 'Copy of Event Schedule' that I uploaded earlier here does have the type of restroom stall column that I am referencing. All the events do fall under row 181 under 'Event Registrations' where the date has meaning. Then we filter out which type of trailer each event or long term event needs and then designate a specific trailer after that. But the count under the category 'Restroom Trailer' would be sufficient if I can get that number. The other date column is to the right and is labeled Event (or Job) End Date.
-
Again, thank you for your help. It is very beneficial and I appreciate your time.
-
=COUNT(CHILDREN([Restroom Trailer]181) - COUNTIFS(CHILDREN([Event (or job) Start Date]181), @cell >= [Event (or job) End Date]@row, CHILDREN([Event (or job) End Date]181), @cell >= [Event (or job) Start Date]@row)
I do also notice that the formatting for your date cells are inconsistent. How are the dates being entered? Are we 100% positive that they are in fact date values and not just text values that look like dates?
-
Possibly, the dates are coming in from a 'form submission' on another sheet. Once we book an event then we have a button that copies that row to this sheet to help eliminate errors. The columns themselves are date columns but I do see what you mean on the formatting being inconsistent. I'll work with this tomorrow and see what progress I can make on it and reach out from there.
-
The one item that threw that formula off was the dates at the cell were both greater than the dates in the children. Once I changed one of those around it gave me a count that changed when I changed the dates in my availability row cells. Although when I manually counted them I couldn't get that to match up exactly.
So I can get a count of the number of trailers out over a given period of time. However this doesn't take into account only weekend usage and having that same trailer returned after the weekend making it available again. I'm not sure that formula can be accomplished.
What if we try this, I can run a formula that gives me the number of trailers booked each day and have that listed out as # of 2 stalls available, # of 4 stalls available, etc. Then I would want to take the Max number found within that range of days (so the maximum number that is booked out on a given day). Then I could take our overall inventory of that number of stalls and subtract it from that max number.
So, I want to find =MAX(CHILDREN([Stalls Available]130)), but I only want to find that number if it falls between two dates that I would input.
Does that make sense?
-
But I want the date to fall between the children of a row.
Help Article Resources
Categories
Check out the Formula Handbook template!
