Modified Date loses detail when referenced

Rob W.

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.

Paul Newcome

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

Paul Newcome

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

Rob W.

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.

Andrée Starå

Hi @Rob W.

I hope you're well and safe!

Here's an excellent, help article about the brackets.

https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula

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!

Paul Newcome

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

Rob W.

https://community.smartsheet.com/discussion/comment/383033#Comment_383033

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!

Rob W.

https://community.smartsheet.com/discussion/comment/383050#Comment_383050

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.

Andrée Starå

@Rob W.

You're more than welcome!

I don't think the +"" trick is mentioned anywhere. Maybe in the Formula Handbook that Paul referenced.

https://www.smartsheet.com/marketplace/template-sets/formula-handbook

✅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!