How to return only the most recent entry before a targeted date range?

JJL
JJL ✭✭✭
in Formulas and Functions

Hello,

I currently have the following asset booking sheet:

image.png

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!

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)")

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)")

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)")

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • JJL
    JJL ✭✭✭
    https://community.smartsheet.com/discussion/comment/332464#Comment_332464

    @Paul Newcome thank you! This worked perfectly!

    · Share on FacebookShare on Twitter
  • JJL
    JJL ✭✭✭
    edited 06/09/22
    https://community.smartsheet.com/discussion/comment/332464#Comment_332464

    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):

    image.png

    What would you recommend to fix this?

    Thank you!

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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))

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • JJL
    JJL ✭✭✭
    https://community.smartsheet.com/discussion/comment/332535#Comment_332535

    Hi @Paul Newcome ,

    After the adjustment, the issue is still occurring unfortunately

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)")

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • JJL
    JJL ✭✭✭
    https://community.smartsheet.com/discussion/comment/332577#Comment_332577

    @Paul Newcome

    That solved the issue perfectly! Thanks again for all of your help :)

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • JJL
    JJL ✭✭✭
    edited 06/21/22

    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:

    image.png

    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:

    image.png

    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!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!