Display Most Recent Created Date AND Time

Options
kelly906
kelly906 ✭✭✭
edited 04/26/22 in Smartsheet Basics

I created a simple check-in form whereby the system Created Date field is to be the entry time. It will be a running sheet where all punches are stored so, for reporting purposes, need to pull out only their most recent date/time for display - the 'time' piece being my challenge. (Also have "Check-in / Arrival" criteria set)

What I've tried...

Max(Collect) shows only the most recent entry but drops the time... (Row 1 in the pic)

Join(Collect) shows the time but pulls in all values... (Row 2 in the pic)

Max(Join(Collect)) makes it all go blank. (Row 3 in the pic)

Feeling like I'm missing something REALLY simple???



Tags:

Best Answer

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @kelly906

    I hope you're well and safe!

    Try adding +"" at the end of the formula (Untested)

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @kelly906

    this is an interesting one. The system Created Date field, is technically a specialized date column. Based on what you are showing your most recent check-in column is a date column (if not you would end up with #INVALID COLUMN VALUE error).

    But, because your field is a standard date field it wont by default show the time as well...

    To get around this, use a version of the formula below, adding in your collects based on you criterion within both MAX functions:

    =RIGHT(MAX({Created Range}), LEN(MAX({Created Range})))

  • kelly906
    kelly906 ✭✭✭
    edited 04/26/22
    Options

    Leibel/Andree - Thanks for such quick replies!

    @Leibel Shuchat , I got the max date AND time to display using your formula (THANK YOU!). That said, I'm messing it up when I try to plug in criteria on 'personnel ID' and 'check-in' type (vs. check-out). Should I use 'collect' with this formula? (Trying to get to "*** is the most recent date/time that *** checked IN for work")

    @Andrée Starå , I tried the + and it turns the formerly blank field to a "0". Included the formula, just to make sure I've plugged it in correctly! =MAX(JOIN(COLLECT([Date and Time Recorded (approximate)]:[Date and Time Recorded (approximate)], [Personnel ID#]:[Personnel ID#], [Personnel ID#]@row))) + "" (Referenced this thread: https://tinyl.io/68ig)


    REALLY appreciate your guys' help on this!! 🙏

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @kelly906

    Yes, add the collect within both MAX functions

  • kelly906
    kelly906 ✭✭✭
    Options

    Sooooo thankful for your help, @Leibel Shuchat! Pulled together the question and the final formula below, just in case it's helpful for someone else looking to do the same thing-ish down the road.


    "Using the system Created Date to display the most recent date and time, per employee for check-ins only (not check-outs)"...

    =RIGHT(MAX(COLLECT([Date and Time Recorded (approximate)]:[Date and Time Recorded (approximate)], [Personnel ID#]:[Personnel ID#], [Personnel ID#]@row, [Checking in or out?]:[Checking in or out?], "Check-in / Arrival")), LEN(MAX(COLLECT([Date and Time Recorded (approximate)]:[Date and Time Recorded (approximate)], [Personnel ID#]:[Personnel ID#], [Personnel ID#]@row, [Checking in or out?]:[Checking in or out?], "Check-in / Arrival"))))

    The final result... 🏆️