Modified Date loses detail when referenced

Rob W.
Rob W. ✭
inFormulas and Functions

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.

· Share on Facebook Share on Twitter

Best Answer

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
@Kris Peeters<\/a> <\/p>

you should be able to use =Countifs([Al Javor]@row:[Lisa Young]@row,\"1 - High\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108307,"type":"question","name":"Formula to Average Performance Score for Various Service Categories","excerpt":"I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has \"civil engineer\" selected, I want a running formula that averages all the civil engineer ratings. I have tried using the =averageif() formula, but I continue to…","snippet":"I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has \"civil engineer\"…","categoryID":322,"dateInserted":"2023-07-31T15:35:13+00:00","dateUpdated":"2023-07-31T15:48:17+00:00","dateLastComment":"2023-07-31T18:57:50+00:00","insertUserID":164346,"insertUser":{"userID":164346,"name":"ullkay95","url":"https:\/\/community.smartsheet.com\/profile\/ullkay95","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bmtAmxLQVL0!e6DCx07vJ9c!25n9oP55COS","dateLastActive":"2023-07-31T18:59:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":164346,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":31,"score":null,"hot":3381654183,"url":"https:\/\/community.smartsheet.com\/discussion\/108307\/formula-to-average-performance-score-for-various-service-categories","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108307\/formula-to-average-performance-score-for-various-service-categories","format":"Rich","lastPost":{"discussionID":108307,"commentID":388084,"name":"Re: Formula to Average Performance Score for Various Service Categories","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388084#Comment_388084","dateInserted":"2023-07-31T18:57:50+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/2HQ91LNQF3GG\/screenshot-2023-07-31-114610.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-07-31 114610.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-31T18:52:12+00:00","dateAnswered":"2023-07-31T18:48:05+00:00","acceptedAnswers":[{"commentID":388078,"body":"

In that case you would use the same syntax but you would reference the column in the sheet using the appropriate column name. <\/p>

[Column name]:[Column name]<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108278,"type":"question","name":"How do you calculate the average number of days between a submitted date and a start date?","excerpt":"Additional context: We use Smartsheet to track support requests from the organization. We have an automated column that records the day the form is filled out. The form also has a date range of when they need their request completed by. I would like to calculate the average amount of days people provide from the day they…","snippet":"Additional context: We use Smartsheet to track support requests from the organization. We have an automated column that records the day the form is filled out. The form also has a…","categoryID":322,"dateInserted":"2023-07-28T20:36:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T21:00:45+00:00","insertUserID":128090,"insertUser":{"userID":128090,"name":"Carlos Fernandez-Torres","url":"https:\/\/community.smartsheet.com\/profile\/Carlos%20Fernandez-Torres","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T21:30:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":40,"score":null,"hot":3381156447,"url":"https:\/\/community.smartsheet.com\/discussion\/108278\/how-do-you-calculate-the-average-number-of-days-between-a-submitted-date-and-a-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108278\/how-do-you-calculate-the-average-number-of-days-between-a-submitted-date-and-a-start-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108278,"commentID":387924,"name":"Re: How do you calculate the average number of days between a submitted date and a start date?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387924#Comment_387924","dateInserted":"2023-07-28T21:00:45+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/QO67SBLMOJQA\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T21:01:27+00:00","dateAnswered":"2023-07-28T21:00:45+00:00","acceptedAnswers":[{"commentID":387924,"body":"

=[End Date]@row - [Start Date]@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions