Asset Availability Formula

Conor Wilson

I am trying to create a formula which allows me to input 2 sets of dates (a check-out date and a due date) which will then present me with the availability of the total assets between those dates.

Does anyone have a formula that calculates availability based of existing availability/unavailability?

I hope this makes sense.

I have attached the sheet to this post.

Much appreciated.

Sheet - Asset Tracker (2).pdf

Genevieve P.

Hi @Conor Wilson

Thank you for the PDF, that's very helpful! However can you clarify what you mean in terms of "availability of the total assets between those dates"?

Are you looking to have the "Asset Status" column automatically update based on Today's Date and what's in the current row?

For example, we could write a nested IF statement that looks to see if the Checked Out date is either Today or in the Past, and if the Checked In date is blank. That would then mean it the status is currently "Checked - Out".

The first thing we'd check is if the Checked-Out date column is blank. If it is, that means the current row is actually "Checked-In"

=IF([Assignee Checked-Out Date]@row = "", "Checked-In",

Then we can write the next IF statement, looking for our other conditions:

IF(AND([Assignee Checked-Out Date]@row <= TODAY(), [Assignee Check-In Date]@row = "", [Assignee Due Date]@row > TODAY()), "Checked - Out"

Otherwise, it's "Overdue":

=IF([Assignee Checked-Out Date]@row = "", "Checked-In", IF(AND([Assignee Checked-Out Date]@row <= TODAY(), [Assignee Check-In Date]@row = "", [Assignee Due Date]@row > TODAY()), "Checked - Out", "Overdue"))

If I've misunderstood your question, it would be helpful to see an example of the formula output you're wanting, identifying what column it would appear in.

Thanks!

Genevieve

Conor Wilson

Hi @Genevieve P. ,

Apologies for the delayed reply.

I have attached an updated PDF to this comment so this whole request should hopefully make more sense.

What I am trying to achieve is putting 2 dates in the sheet, 1 in the "Check-Out Date Column" and 1 in the "Due Date" column, in the "Laptop Availability" row, then being given the number of laptops available for use during this stated period of time in the "Asset Availability" column.

I'm new to Smartsheet so still trying to figure all the functions out.

The formula I am currently working with is based off of one I found online designed for a similar purpose which I have put below:

=20 - (MAX(COLLECT(CHILDREN([Asset Status]@row), CHILDREN([Check-Out Date]@row), @cell >= [Check-Out Date]@row, CHILDREN([Check-Out Date]@row), @cell <= [Due Date]@row)))

I don't know if this makes any sense or not, as the answer it currently gives me is whatever number I put at the beginning of the formula.

Sheet - Laptop Tracker (5).pdf

As a framework in the sheet I have 14 listed as "Checked-Out", 2 listed as "Maintenance", and 4 as "Checked-In". The "Checked-Out" laptops are checked out between 26th Oct and 31st Oct; the laptops under "Maintenance" are under maintenance until 18th and 31st Oct. The formula hopefully will then look at these dates and compare them to the 2 dates stated at the top of the columns, in the case of the example it is looking for between 29th Oct and 3rd Nov. So in this case the resulting number should be 5 available laptops; with 4 laptops already being "Checked-In" and 1 laptop returning from maintenance.

I hope I have been able to clarify what I am trying to achieve and I appreciate the help.

Thanks,

Conor

Genevieve P.

Hi @Conor Wilson

Thank you for clarifying! I believe I understand now - in the top row (the one immediately beneath your Parent row) you want to know how many of the child rows are available based on the dates selected in that row.

The first thing I would adjust is to make another level of hierarchy so that your "Laptop Availability Check" row is actually the Parent of all the Laptops. Then you can use the CHILDREN function so it looks at all the indented rows.

What I would do is first count all children to find a total:

=COUNT(CHILDREN())

Then subtract the COUNT of child rows that have a Due Date greater than the due date you've put in the current row (meaning it won't be returned until after your date range).

=COUNT(CHILDREN()) - COUNTIFS(CHILDREN([Due Date]@row), >=[Due Date]@row)

Keep in mind that this will still count rows where the return date is in the middle of your range. For example, say you select a Monday-to-Friday range, and the laptop is due back on the Thursday, this will count it as being "available" because by Friday it will be. Does that make sense?

Let me know if adding the additional hierarchy and formula worked for you!

Cheers,

Genevieve