Display Most Recent Created Date AND Time
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???
Best Answer
-
Yes, add the collect within both MAX functions
Answers
-
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.
-
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})))
-
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!! 🙏
-
Yes, add the collect within both MAX functions
-
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... 🏆️
-
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 :(
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives