Delimit Date/time in a match/index function

Hi all,

I'm using a match/index function currently to pull over data from another sheet.

Currently I'm using this:

=IFERROR(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), "")


The sheet I'm referencing in the function has time and date in the same column, so the above formula pulls over data that looks like this:2023-06-17T12:30:53-04:00

I'm hoping toonly pull over the date, not the time. I suspect the MID functionwould be appropriate to add into my formula, but I've never actually used it. Asking for help to see if this possible? if so, how would I add that into my current formula.

Apologies if I'm missing any necessary context.


Thank you!

Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The DATEONLY function will not work because it was designed to pull the date out of something that is already stored as a date or date/time stamp on the back-end. The above data is a text string.


    Try this instead:

    =IFERROR(DATE(VALUE(LEFT(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 4)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 6, 2)), VALUE(MID(INDEX({Google Sheets PTI Daily Form 6/19/23 Range 7}, MATCH([email protected], {claim num}, 0)), 9, 2))), "")

    thinkspi.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    What happens when you remove the VALUE from it?


    =SUBSTITUTE(SUBSTITUTE([Column name]@row, "$", ""), "USD", "")

    thinkspi.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi @steinkj<\/a>, change the \"HAS\" to an equal.<\/p>


<\/p>

=COUNTIF([Tools & Resources (SE-2)]8:[Tools & Resources (SE-2)]12, \"Often\")<\/p>"},{"commentID":382723,"body":"

Thank you, as always I was trying to make it too hard!!!!<\/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":106939,"type":"question","name":"How to extract part of a cell and remove the rest","excerpt":"Hello I have a very long column with various texts in every cell containing for example 'Data change request from John Smith', every cell contains different types of requests and I want to extract to another column only the text before the word 'request '. Could you please help me how to do this? Thank you so much!","snippet":"Hello I have a very long column with various texts in every cell containing for example 'Data change request from John Smith', every cell contains different types of requests and…","categoryID":322,"dateInserted":"2023-06-27T09:11:14+00:00","dateUpdated":null,"dateLastComment":"2023-06-28T09:51:15+00:00","insertUserID":143328,"insertUser":{"userID":143328,"name":"Christiana Gkini","url":"https:\/\/community.smartsheet.com\/profile\/Christiana%20Gkini","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtf-hNm8WhOgG9NXB7Zou6oQaUyDHZZ2iwrHmrrz=s96-c","dateLastActive":"2023-06-28T09:49:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T09:51:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3375804749,"url":"https:\/\/community.smartsheet.com\/discussion\/106939\/how-to-extract-part-of-a-cell-and-remove-the-rest","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106939\/how-to-extract-part-of-a-cell-and-remove-the-rest","format":"Rich","lastPost":{"discussionID":106939,"commentID":382775,"name":"Re: How to extract part of a cell and remove the rest","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382775#Comment_382775","dateInserted":"2023-06-28T09:51:15+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-28T09:51:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T09:27:10+00:00","dateAnswered":"2023-06-27T11:27:01+00:00","acceptedAnswers":[{"commentID":382494,"body":"

Hi @Christiana Gkini<\/a>,<\/p>

If the format of the entries is consistent then you can use a formula similar to this:<\/p>

=LEFT([Column to check]@row, (FIND(\"request\", [Column to check]@row) - 2))<\/p>

Example:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Obviously you will probably need to change the \"Column to check\" in the formula, but this should be straightforward enough.<\/p>

Hope this helps; any questions or issues then just post! 😊<\/span><\/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":106973,"type":"question","name":"SUMIFS with CONTAINS","excerpt":"Hello, I want to write a formula that SUMS charges (\"Ext Price\" column) from all item descriptions that contain a word (\"CF\") from an accounting \"Activity Description\" column. I've tried various versions of this. =SUMIFS(CONTAINS({Activity Description}, \"CF\", {Ext Price}) =SUMIFS({Activity Description}, CONTAINS(CF), {Ext…","snippet":"Hello, I want to write a formula that SUMS charges (\"Ext Price\" column) from all item descriptions that contain a word (\"CF\") from an accounting \"Activity Description\" column.…","categoryID":322,"dateInserted":"2023-06-27T19:19:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T21:26:52+00:00","insertUserID":162846,"insertUser":{"userID":162846,"name":"UserAHC","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/UserAHC","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3bF0P9bUFHs!!W5i-ZnFYhF2","dateLastActive":"2023-06-27T21:25:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162846,"lastUser":{"userID":162846,"name":"UserAHC","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/UserAHC","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3bF0P9bUFHs!!W5i-ZnFYhF2","dateLastActive":"2023-06-27T21:25:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3375795958,"url":"https:\/\/community.smartsheet.com\/discussion\/106973\/sumifs-with-contains","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106973\/sumifs-with-contains","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106973,"commentID":382705,"name":"Re: SUMIFS with CONTAINS","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382705#Comment_382705","dateInserted":"2023-06-27T21:26:52+00:00","insertUserID":162846,"insertUser":{"userID":162846,"name":"UserAHC","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/UserAHC","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!3bF0P9bUFHs!!W5i-ZnFYhF2","dateLastActive":"2023-06-27T21:25:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-28T09:44:38+00:00","dateAnswered":"2023-06-27T19:47:23+00:00","acceptedAnswers":[{"commentID":382674,"body":"

Try this:<\/p>

=SUMIFS({Ext Price}, {Activity Description}, CONTAINS(\"CF\", @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":[{"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