Display Most Recent Created Date AND Time

kelly906

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

Leibel S

@kelly906

Yes, add the collect within both MAX functions

Andrée Starå

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!

Leibel S

@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

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

@kelly906

Yes, add the collect within both MAX functions

kelly906

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... 🏆️

firestorm

hello @kelly906 thanks for the solution but could you explain how that works? I can't comprehend the formula :)
however, if it's the same date/time, then it doesn't work :(