Pulling specific modifications dates from the past
Hi! I am tracking documents that need to be signed. I have a status column that tracks if it's "signed", "unsigned", or "refused". I'm needing to go back and collect the dates that they were turned to "refused". Too much time has gone by, and so the modification date field doesn't work. Looking at each cell history would take too much time to go one by one (there's about 2000 records)... is there a better way to do this?
I'm open to any ideas! Thank you :)
Answers
-
Kelly Drake Overachievers AlumniNot sure if this idea scales well or not - but wondering if you could look at the activity log to pull that data - you'd really need to manipulate the log to gt to the data point you're looking for though. I've not found a scalable/automated way to do this on historical data.
Kelly Drake (she/her/hers)
STARBUCKS COFFEE COMPANY| senior systems analyst | global growth & concepts
-
Andrée Starå ✭✭✭✭✭✭I think the only solution would be a 3rd party solution or the Smartsheet API.
Are those viable options?
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 help the Community bymarking it as the accepted answer/helpful. 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:[email protected]| 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
Got this to work: =COUNTIFS([Approval Confirmed]:[Approval Confirmed], 1, [Thankyou Made]:[Thankyou Made], 1)<\/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":112141,"type":"question","name":"A formula to return characters between text and the next semicolon in a cell","excerpt":"Column A always contains the information \"PLAN 1234;\" The semicolon is always the third semicolon. The plan numbers are alphanumeric and can be any length. I need to return just the plan number without \"PLAN \" but not sure what I'm misunderstanding about how to use FIND, LEN, etc. I can get the individual functions to work…","snippet":"Column A always contains the information \"PLAN 1234;\" The semicolon is always the third semicolon. The plan numbers are alphanumeric and can be any length. I need to return just…","categoryID":322,"dateInserted":"2023-10-25T16:28:08+00:00","dateUpdated":null,"dateLastComment":"2023-10-25T21:58:07+00:00","insertUserID":153822,"insertUser":{"userID":153822,"name":"Coffee","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Coffee","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!wUstsDoD0co!2qDu_9543Ao!fp9suuvKqn4","dateLastActive":"2023-10-25T21:44:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":15741,"lastUser":{"userID":15741,"name":"ker9","url":"https:\/\/community.smartsheet.com\/profile\/ker9","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-25T21:58:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":30,"score":null,"hot":3396524175,"url":"https:\/\/community.smartsheet.com\/discussion\/112141\/a-formula-to-return-characters-between-text-and-the-next-semicolon-in-a-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112141\/a-formula-to-return-characters-between-text-and-the-next-semicolon-in-a-cell","format":"Rich","lastPost":{"discussionID":112141,"commentID":401718,"name":"Re: A formula to return characters between text and the next semicolon in a cell","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401718#Comment_401718","dateInserted":"2023-10-25T21:58:07+00:00","insertUserID":15741,"insertUser":{"userID":15741,"name":"ker9","url":"https:\/\/community.smartsheet.com\/profile\/ker9","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-25T21:58:14+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-25T21:15:28+00:00","dateAnswered":"2023-10-25T21:15:23+00:00","acceptedAnswers":[{"commentID":401708,"body":"
This worked:<\/p>
=MID(A@row, FIND(\"PLAN \", A@row) + 5, VALUE(FIND(\";\", A@row, FIND(\"PLAN \", A@row))) - VALUE(FIND(\"PLAN \", A@row) + 5))<\/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":112139,"type":"question","name":"multiple NOT(CONTAINS) not working","excerpt":"Hello, i hope someone can help me solve this puzzle. I'm calculating the number of rows in my sheet based on some criteria in one column (Division), but I need to exclude 3 people (rows) from it temporarily based on another column (Last Name). I'm able to exclude one, but I'm not able to add the exclusions for the other…","snippet":"Hello, i hope someone can help me solve this puzzle. I'm calculating the number of rows in my sheet based on some criteria in one column (Division), but I need to exclude 3 people…","categoryID":322,"dateInserted":"2023-10-25T16:25:27+00:00","dateUpdated":null,"dateLastComment":"2023-10-25T21:08:14+00:00","insertUserID":157776,"insertUser":{"userID":157776,"name":"OshaK","url":"https:\/\/community.smartsheet.com\/profile\/OshaK","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T01:41:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":157776,"lastUser":{"userID":157776,"name":"OshaK","url":"https:\/\/community.smartsheet.com\/profile\/OshaK","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T01:41:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3396520421,"url":"https:\/\/community.smartsheet.com\/discussion\/112139\/multiple-not-contains-not-working","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112139\/multiple-not-contains-not-working","format":"Rich","lastPost":{"discussionID":112139,"commentID":401699,"name":"Re: multiple NOT(CONTAINS) not working","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401699#Comment_401699","dateInserted":"2023-10-25T21:08:14+00:00","insertUserID":157776,"insertUser":{"userID":157776,"name":"OshaK","url":"https:\/\/community.smartsheet.com\/profile\/OshaK","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T01:41:10+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-25T22:26:31+00:00","dateAnswered":"2023-10-25T20:37:24+00:00","acceptedAnswers":[{"commentID":401687,"body":"
Try: =COUNTIFS([Division]:[Division], OR(@cell = \"Div 1\", @cell = \"Div 2\", @cell = \"Div 3\", @cell = \"Div 4\", @cell = \"Div 5\"), [Last Name]:[Last Name], AND(NOT(CONTAINS(\"Person 1\", @cell)), NOT(CONTAINS(\"Person 2\", @cell))<\/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":[]}],"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":[]}">
