How to return only the most recent entry before a targeted date range?
Hello,
I currently have the following asset booking sheet:
What I'm trying to do is, for the column 'Assignee in Possession Before Your Booking Date:', if the asset is available within the booking date range that the user has entered -- I would like the column to display what assignee will have the asset last (most recently?) before the booking range that the user has entered.
My current formula is:
=IF([Asset Available During Your Booking Date Range?]@row <> "AVAILABLE", "", JOIN(COLLECT([Assignee(s):]:[Assignee(s):], [Asset ID:]:[Asset ID:], [Asset ID:]@row, [Asset Available During Your Booking Date Range?]:[Asset Available During Your Booking Date Range?], "AVAILABLE", [Asset Booking Start Date:]:[Asset Booking Start Date:], @cell < [Asset Booking Start Date:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell < [Asset Booking Start Date:]@row)))
Unfortunately, what my formula is doing is joining and displaying every single assignee that will have the asset before the user's entered booking date range. E.g., In the image above, for the fourth row, the column should only display 'John' but instead is displaying 'JohnLucas'.
I've tried incorporating the MAX function into my formula to try to get the most recent assignee, but have had no success thus far. Additionally, as the sheet's entries will be erased every other month or so, there will be cases where no one will be in possession of the asset before the user's booking date range in which case my formula is just returning a blank cell -- instead, I'd like it to say something like "NO PREVIOUS ASSIGNEE(S)"
Any help is greatly appreciated, thank you!
Best Answers
-
You will need a helper column to first grab the nearest end date prior to the row's start date.
=MAX(COLLECT([Asset Booking End Date:]:[Asset Booking End Date:], [Asset Booking End Date:]:[Asset Booking End Date:], @cell < [Asset Booking Start Date:]@row, [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row))
Then in your display column you would use this:
=IFERROR(INDEX(COLLECT([Assignee(s):]:[Assignee(s):], [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell = [Helper Column]@row), 1), "NO PREVIOUS ASSIGNEE(S)")
-
I see the issue. Try this:
=IF([Nearest Booking Date:]@row <> "", INDEX(COLLECT([Assignee(s):]:[Assignee(s):], [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell = [Helper Column]@row), 1), "NO PREVIOUS ASSIGNEE(S)")
Answers
-
You will need a helper column to first grab the nearest end date prior to the row's start date.
=MAX(COLLECT([Asset Booking End Date:]:[Asset Booking End Date:], [Asset Booking End Date:]:[Asset Booking End Date:], @cell < [Asset Booking Start Date:]@row, [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row))
Then in your display column you would use this:
=IFERROR(INDEX(COLLECT([Assignee(s):]:[Assignee(s):], [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell = [Helper Column]@row), 1), "NO PREVIOUS ASSIGNEE(S)")
-
@Paul Newcome thank you! This worked perfectly!
-
Hi @Paul Newcome,
I just have a quick question regarding the formula -- I noticed that when there is a blank date in any one of the booking date columns, it results in the formula stating that the blank date is the most recent date and therefore, outputs a result with the applicable assignee that has the blank dates (e.g., in the image below Bob shows up as the last assignee in possession for Steve):
What would you recommend to fix this?
Thank you!
-
Try this adjustment:
=MAX(COLLECT([Asset Booking End Date:]:[Asset Booking End Date:], [Asset Booking End Date:]:[Asset Booking End Date:], AND(@cell < [Asset Booking Start Date:]@row, @cell <> ""), [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row))
-
Hi @Paul Newcome ,
After the adjustment, the issue is still occurring unfortunately
-
I see the issue. Try this:
=IF([Nearest Booking Date:]@row <> "", INDEX(COLLECT([Assignee(s):]:[Assignee(s):], [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell = [Helper Column]@row), 1), "NO PREVIOUS ASSIGNEE(S)")
-
That solved the issue perfectly! Thanks again for all of your help :)
-
Happy to help. 👍️
-
Hi @Paul Newcome ,
My apologies for the barrage of questions but I thought I would try reaching out on this thread rather than creating a new question as I noticed that you're the person that's been answering all of my questions that I've posted haha (thanks again for your help!)
For the above asset booking system, I received feedback that I should add a time component to the booking system:
Unfortunately, I haven't been able to get it to work correctly as, in the above screenshot, you can see that the booker is stating the asset is available when it is not. Additionally, the 'Assignee in Possession Before Your Booking Date:' column is not functioning correctly anymore.
My formulas for the columns are as follows:
Asset Available During Your Booking Date Range?
=IF([Asset Booking End Time:]@row = "", "", IF([Asset Booking End Date:]@row <> "", IF(COUNTIFS([Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row, [Asset Booking Start Date:]:[Asset Booking Start Date:], @cell <= [Asset Booking End Date:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell >= [Asset Booking Start Date:]@row, [Asset Booking Start Time Converter:]:[Asset Booking Start Time Converter:], @cell <= [Asset Booking End Time Converter:]@row, [Asset Booking End Time Converter:]:[Asset Booking End Time Converter:], @cell >= [Asset Booking Start Time Converter:]@row, [Asset Booking Priority Rank:]:[Asset Booking Priority Rank:], @cell < [Asset Booking Priority Rank:]@row) > 0, "NOT AVAILABLE", "AVAILABLE")))
Assignee in Possession Before Your Booking Date:
=IF([Asset Available During Your Booking Date Range?]@row <> "AVAILABLE", "", IF([Nearest Booking Date:]@row <> "", INDEX(COLLECT([Assignee(s):]:[Assignee(s):], [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row, [Asset Booking End Date:]:[Asset Booking End Date:], @cell = [Nearest Booking Date:]@row, [Nearest Booking Time:]:[Nearest Booking Time:], @cell < [Nearest Booking Time:]@row), 1), "NO PREVIOUS ASSIGNEE(S)"))
I have the following helper columns:
Asset Booking Priority Rank:
This is an auto-number column
Nearest Booking Date:
=MAX(COLLECT([Asset Booking End Date:]:[Asset Booking End Date:], [Asset Booking End Date:]:[Asset Booking End Date:], @cell <= [Asset Booking Start Date:]@row, [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row))
Nearest Booking Time:
=MAX(COLLECT([Asset Booking End Time Converter:]:[Asset Booking End Time Converter:], [Asset Booking End Time Converter:]:[Asset Booking End Time Converter:], @cell < [Asset Booking Start Time Converter:]@row, [Asset ID:]:[Asset ID:], @cell = [Asset ID:]@row))
I've run into a problem with the above formula as it returns '0' if it does not find a match -- which is problematic in 24 hour time as midnight is typically 0, so in the time converter columns below, I've assigned midnight a value of 10 to get around this.
The Asset Booking Time Converter columns just use the following IF statements (as Smartsheet does not appear to accept time entries, I've assigned the various time increments a 24 hour numeric value), as the formulas are really long, I've just posted the formula for one of the columns below:
=IF([Asset Booking Start Time:]@row = "12:00 AM", 10, IF([Asset Booking Start Time:]@row = "12:30 AM", 30, IF([Asset Booking Start Time:]@row = "1:00 AM", 100, IF([Asset Booking Start Time:]@row = "1:30 AM", 130, IF([Asset Booking Start Time:]@row = "2:00 AM", 200, IF([Asset Booking Start Time:]@row = "2:30 AM", 230, IF([Asset Booking Start Time:]@row = "3:00 AM", 300, IF([Asset Booking Start Time:]@row = "3:30 AM", 330, IF([Asset Booking Start Time:]@row = "4:00 AM", 400, IF([Asset Booking Start Time:]@row = "4:30 AM", 430, IF([Asset Booking Start Time:]@row = "5:00 AM", 500, IF([Asset Booking Start Time:]@row = "5:30 AM", 530, IF([Asset Booking Start Time:]@row = "6:00 AM", 600, IF([Asset Booking Start Time:]@row = "6:30 AM", 630, IF([Asset Booking Start Time:]@row = "7:00 AM", 700, IF([Asset Booking Start Time:]@row = "7:30 AM", 730, IF([Asset Booking Start Time:]@row = "8:00 AM", 800, IF([Asset Booking Start Time:]@row = "8:30 AM", 830, IF([Asset Booking Start Time:]@row = "9:00 AM", 900, IF([Asset Booking Start Time:]@row = "9:30 AM", 930, IF([Asset Booking Start Time:]@row = "10:00 AM", 1000, IF([Asset Booking Start Time:]@row = "10:30 AM", 1030, IF([Asset Booking Start Time:]@row = "11:00 AM", 1100, IF([Asset Booking Start Time:]@row = "11:30 AM", 1130, IF([Asset Booking Start Time:]@row = "12:00 PM", 1200, IF([Asset Booking Start Time:]@row = "12:30 PM", 1230, IF([Asset Booking Start Time:]@row = "1:00 PM", 1300, IF([Asset Booking Start Time:]@row = "1:30 PM", 1330, IF([Asset Booking Start Time:]@row = "2:00 PM", 1400, IF([Asset Booking Start Time:]@row = "2:30 PM", 1430, IF([Asset Booking Start Time:]@row = "3:00 PM", 1500, IF([Asset Booking Start Time:]@row = "3:30 PM", 1530, IF([Asset Booking Start Time:]@row = "4:00 PM", 1600, IF([Asset Booking Start Time:]@row = "4:30 PM", 1630, IF([Asset Booking Start Time:]@row = "5:00 PM", 1700, IF([Asset Booking Start Time:]@row = "5:30 PM", 1730, IF([Asset Booking Start Time:]@row = "6:00 PM", 1800, IF([Asset Booking Start Time:]@row = "6:30 PM", 1830, IF([Asset Booking Start Time:]@row = "7:00 PM", 1900, IF([Asset Booking Start Time:]@row = "7:30 PM", 1930, IF([Asset Booking Start Time:]@row = "8:00 PM", 2000, IF([Asset Booking Start Time:]@row = "8:30 PM", 2030, IF([Asset Booking Start Time:]@row = "9:00 PM", 2100, IF([Asset Booking Start Time:]@row = "9:30 PM", 2130, IF([Asset Booking Start Time:]@row = "10:00 PM", 2200, IF([Asset Booking Start Time:]@row = "10:30 PM", 2230, IF([Asset Booking Start Time:]@row = "11:00 PM", 2300, IF([Asset Booking Start Time:]@row = "11:30 PM", 2330, ""))))))))))))))))))))))))))))))))))))))))))))))))
Any help is greatly appreciated, thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!
