Modified Date loses detail when referenced
Application: Trying to capture and display a 'sheet last modified' value on a dashboard.
Approach: Using a formula in the sheet summary sidebar to find the max value of all timestamps in the 'Modified' column. Formula is as follows and is functioning as expected.
=MAX([Modified]:[Modified])
Problem: The displayed value from the formula is the correct date, but loses the timestamp detail (HH:MM). This can work in applications where the update frequency is weekly or every few days, but in cases where we need to verify if data collection has occurred following a shift, or after an update request was sent, for example, this timestamp data is critical to the application intent.
I don't want to use notifications for this, as this affects a large distribution list and isn't something we want to generate non-value-added communications around.
Ideas:
The data type options in the sheet summary includes 'date', but not 'modified date' which appears to be it's own metadata format type developed specifically for the 'modified' timestamp function, similar to other Smartsheet custom row metadata types for 'Latest Comment', 'Created By', and 'Created'.
I've tried pulling this value in as text vs. a date, but this throws an 'invalid column value' error. Wondering if there's a way to convert this to text through a helper column or formula function that would eliminate the data loss.
Appreciate any feedback on how to bridge this gap.
Best Answer
-
Set the Sheet Summary field as text/number then add +"" to the end of the MAX function (plus quote quote) to convert it into a text string.
=MAX([Modified]:[Modified]) + ""
Answers
-
Set the Sheet Summary field as text/number then add +"" to the end of the MAX function (plus quote quote) to convert it into a text string.
=MAX([Modified]:[Modified]) + ""
-
Thanks Paul! This did the trick and is much cleaner than the multi-step workarounds I was anticipating might be needed here.
Is there a published list of Smartsheet character-based syntax modifiers / boolean operators out there? I don't see anything in the functions list - https://help.smartsheet.com/functions or formula basics - https://help.smartsheet.com/learning-track/level-1-get-started/formula-basics that speaks to the use of @, +, { }, {{ }}, [ ], or others. A lot of this comes with practice, but would be nice for onboarding to be able to point to a simple introduction.
-
Hi @Rob W.
I hope you're well and safe!
Here's an excellent, help article about the brackets.
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, Awesome, 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.
-
Happy to help. 👍️
I would suggest the Formula Handbook template that can be found on the right side of this page directly below the Quick Links (closer to the top of the page).
-
Thanks Andree, this is pretty much what I was looking for, though interestingly doesn't actually address the function of using + "" to convert to plain text. Still, this is a good one-pager intro to the basic syntax of formulas, much appreciated!
-
Thanks Paul, hadn't noticed that there on the sidebar.
This is pretty comprehensive and will be a good working template and reference / intro for some of our intermediate users. I learned a lot of this nuance organically via the formula prompts and through working sessions as part of solution deployment, but getting users up to speed without having to wade through the same process is key.
-
You're more than welcome!
I don't think the +"" trick is mentioned anywhere. Maybe in the Formula Handbook that Paul referenced.
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome 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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!