Check if Item is Available based on dates input

Kyle Jarrett

This may be an odd one.. Backstory:

We are attempting to replace a check in/check out feature we have for our internal associates to reserve items from our Marketing Team for tradeshows or other events. The workflow is; the user would look up an item (I have implemented an item inventory number for a unique identifier) preferably on a dashboard where they are only able to suggest an item and a date range. Once they select their item and their "Date Needed" and "Return Date" ideally this would look at all of the other reservations and return an output stating if this item is available for the specified date range.

I tried to do this with an IF, AND statement and I failed miserably so wanted to get some of the community involved to hopefully help out!

=IF({Community Tools Descriptions - Checkout Sh Range 3} = [Primary Column]@row), AND([Column3]@row >= {Community Tools Descriptions - Checkout Sh Range 1}, [Column3]@row <= {Community Tools Descriptions - Checkout Sh Range 2}), "Yes")

p.s. if anyone has a better idea on how users can "check out" and "Check-in" items using smartsheet I would be very open to suggestions. Thanks for you time in advance!

Kyle Jarrett

Here is another formula I came up with which I thought was very close but kept getting #Unparseable.

=IF(AND(COUNTIFS({Item}, [Primary Column]@row)>0,COUNTIFS({Date Needed}, ">="&[Column3]@row, {Date Needed}, "<="&[Column4]@row)>0, COUNTIFS({Returned Date}, ">="&[Column3]@row, {Returned Date}, "<="&[Column4]@row)>0),"Available","Not Available")

Kelly Moore

Hey @Kyle Jarrett

The below formula may work for you - this is based on the logic of your formula above. I added the {Item} criteria to each of your CountIFs - I assumed you were only interested in counting dates when your item is involved?

=IF(AND(COUNTIFS({Item}, [Primary Column]@row)>0,COUNTIFS({Item}, [Primary Column]@row, {Date Needed}, >=[Column3]@row, {Date Needed}, <=[Column4]@row)>0, COUNTIFS({Item}, [Primary Column]@row,{Returned Date}, >=[Column3]@row, {Returned Date}, <=[Column4]@row)>0),"Available","Not Available")

Will this work for you?

Kelly

Kyle Jarrett

Hi @Kelly Moore -

Thank you for your help! I am getting an invalid ref from your formula above. I am getting some results from this one.

=IF(COUNTIFS({Item}, [Primary Column]@row) > 0, IF(OR(AND([Column3]@row >= {Date Needed}, [Column3]@row <= {Return Date}), AND([Column4]@row >= {Date Needed}, [Column4]@row <= {Return Date})), "Not Available", "Available"), "Item Not Found")

but ultimately I would like to know if the item in the row has a record on the other sheet within the specified date range.

Kelly Moore

Hey @Kyle Jarrett

In your formula above, you tried to refer to the date range {Date Needed}. Unless {Date Needed} is a single cell, you are asking smartsheet to try to choose the entire date range and be greater than that. Smartsheet (or any program) doesn't know what to do with that input.

Going back to my formula that you said produced an invalid reference. Typically, this occurs when a user doesn't properly build a cross sheet reference. If you tried to copy paste my formula into your sheet, you dind't properly create the cross sheet references. I wanted to make sure you knew what columns I was referencing on your source sheet so I named your ranges. I suggest going back to my formula, and one at a time, delete the reference from out of the formula, then using the Insert reference link, re-insert the range. You can name it whatever you like.

If you need help with cross sheet references, see this link

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

Let me know if this doesn't help and I will try to give you better instructions on what to do.

Kelly

Kyle Jarrett

Hi @Kelly Moore - so the reference in this case, even in your formula, shouldn't be the entire column of date needed?

I am trying to take the following input:

Item:

Date Needed:

Return Date:

Then, check my cross referenced sheet and check to see if any item matches the item from my formula row and if that item is within that date range (Date Needed and Return Date) in the referenced sheet. if it is I want an unavailable output, if not I would like an available output. Maybe I wasn't on the right track with countifs? I don't necessarily want to count them, I just want to look to see if the item is available during the date range select.

Kyle Jarrett

@Kelly Moore - i'm getting an output now but I am referencing the entire column for the date ranges. so everything is unavailable even when I change the dates to when I know it is. This feels very close.

Kelly Moore

@Kyle Jarrett

Yes, in my formula it the range SHOULD BE the entire column. What I tried to say above is that your new formula is also asking smartsheet to manage an entire column, not a comparison of any individual cells within that column. Your formula will not work.

I understand what you're trying to do- my initial formula should do this. Please re-insert each of the cross sheet references by first deleting them, then reinserting them. Then save the sheet. Once that is done, please let me know what results.

Kelly

Kyle Jarrett

Thank you so much for your time on this @Kelly Moore

I am getting all "Not Available" as the output now.

Here is the formula I have in the column.

=IF(AND(COUNTIFS({Item}, [Primary Column]@row) > 0, COUNTIFS({Item}, [Primary Column]@row, {Date Needed}, >=[Column3]@row, {Date Needed}, <=[Column4]@row) > 0, COUNTIFS({Item}, [Primary Column]@row, {Return Date}, >=[Column3]@row, {Return Date}, <=[Column4]@row) > 0), "Available", "Not Available")

Kelly Moore

Hey Kyle

Try this

=IF((COUNTIFS({Item}, [Primary Column]@row, {Date Needed}, >=[Column3]@row, {Date Returned}, <=[Column4]@row) > 0), "Not Available", "Available")

Kelly