#INVALID COLUMN VALUE

LGraf
LGraf ✭✭
edited 05/18/23 inSmartsheet Basics

I am trying to run some formulas using data including a cell with a date but am receiving an #INVALID COLUMN VALUE error.

The cell, which is actually part of a larger data set organized in a column, is linked from another sheet. Both the original cell and the linked cell are formatted as dates. The same error occurs when trying to use a simple =ISDATE formula with data from other cells regardless of whether they have dates, numbers, or text.

Where am I going wrong?

Tags:
· Share on Facebook Share on Twitter

Best Answer

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

    当使用一个日期值公式,you need to use the DATE function.

    =IF(CONTAINS("Meeting Package 4", [Actualized Services (Awarded Call Tracker)]@row), IF([Date of Award Notification (Awarded Call Tracker)]@row >DATE(2021, 12, 08), "$100"), "$105")

    10xViz.com

    · Share on Facebook Share on Twitter

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@LGraf

    I hope you're well and safe!

    Can you share some screenshots?(Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    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 bymarking 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

    https://workbold.com/

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • LGraf
    LGraf ✭✭

    See screenshot below.

    Screenshot 2023-05-18 132801.png


    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @LGraf

    Can you also paste the formula you're testing?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It looks like you are trying to pull a date value into a non-date type column. Is the column you are putting the formula in set as a date type column?

    10xViz.com

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @LGraf

    Try changing the column to a Date Type or test adding +"" at the end of the formula if you don't want it formatted as a date column.

    Did that work/help?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • LGraf
    LGraf ✭✭

    I wasn't able to get any of those suggestions to work. See formulas and screenshot below.

    [Test formulas 1 (text/number column)]1 contains "=ISDATE([Date of Award Notification (Awarded Call Tracker)]1)"

    [Test formulas 1 (text/number column)]3 contains "=ISDATE([IF CONTAINS MEETING PACKAGE 4 = 1]@row)"

    [Test formulas 1 (text/number column)]5 contains "=ISDATE([Actualized Services (Awarded Call Tracker)]5)"

    [Test formulas 1 (text/number column)]7 contains "=ISDATE([Test formulas 1 (text/number column)]8)"

    [Test formulas 1 (text/number column)]8 contains "05/03/23"

    [Test formulas 2 (date column)]1 contains "=ISDATE([Date of Award Notification (Awarded Call Tracker)]@row)"

    [Test formulas 2 (date column)]6 contains "=IF([Test formulas 2 (date column)]8 > "1/1/2020", "greater", "less")"

    [Test formulas 2 (date column)]7 contains "=ISDATE([Test formulas 2 (date column)]8)"

    [Test formulas 2 (date column)]9 contains "06/12/22"

    The formula I am actually trying to run in [0001]1 contains "=IF(CONTAINS("Meeting Package 4", [Actualized Services (Awarded Call Tracker)]@row), IF([Date of Award Notification (Awarded Call Tracker)]@row > "12/8/21", "$100"), "$105")" but is turning up #INVALID OPERATION due to the original date issue.

    Thanks a ton for your help!

    Screenshot 2023-05-18 151212.png


    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    当使用一个日期值公式,you need to use the DATE function.

    =IF(CONTAINS("Meeting Package 4", [Actualized Services (Awarded Call Tracker)]@row), IF([Date of Award Notification (Awarded Call Tracker)]@row >DATE(2021, 12, 08), "$100"), "$105")

    10xViz.com

    · Share on Facebook Share on Twitter

Categories

\n \n https:\/\/community.smartsheet.com\/discussion\/108888\/is-it-possible-to-url-query-an-embedded-form\n <\/a>\n<\/div>\n

Hi,<\/p>

I hope you're well and safe!<\/p>

Unfortunately, as far as I know, it's not possible now (if you mean that the Query should be included in the URL to Dashboard, it's only possible in the URL to the form), but it's an excellent idea!<\/p>

Please submit this as a Product Feedback or Idea <\/strong>(If it hasn't been added already)<\/em><\/strong> when you have a moment.<\/strong><\/a><\/p>

How would you like to use it? What's the use case?<\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":204,"urlcode":"Forms","name":"Forms"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":292,"urlcode":"Dashboards","name":"Dashboards"},{"tagID":530,"urlcode":"url","name":"URL"}]},{"discussionID":108886,"type":"question","name":"Why does a user show an email address?","excerpt":"When I'm adding users to Assigned, it usually shows the First Name Last Name, but sometimes it just shows the email address. How do I fix this? See screenshot example.","snippet":"When I'm adding users to Assigned, it usually shows the First Name Last Name, but sometimes it just shows the email address. How do I fix this? See screenshot example.","categoryID":321,"dateInserted":"2023-08-12T18:59:03+00:00","dateUpdated":null,"dateLastComment":"2023-08-12T20:33:33+00:00","insertUserID":128842,"insertUser":{"userID":128842,"name":"Karen Bruer","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Karen%20Bruer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5MPAuTrWZX4!elHDp9qhGX4!HMSKFvshvfI","dateLastActive":"2023-08-13T15:19:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":161632,"lastUser":{"userID":161632,"name":"SteyJ","title":"Jacob Stey","url":"https:\/\/community.smartsheet.com\/profile\/SteyJ","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5Zjq-V_rc4o!zmkrczO71bo!6kwXWfi9FUH","dateLastActive":"2023-08-13T22:08:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":22,"score":null,"hot":3383739756,"url":"https:\/\/community.smartsheet.com\/discussion\/108886\/why-does-a-user-show-an-email-address","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108886\/why-does-a-user-show-an-email-address","format":"Rich","lastPost":{"discussionID":108886,"commentID":390375,"name":"Re: Why does a user show an email address?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390375#Comment_390375","dateInserted":"2023-08-12T20:33:33+00:00","insertUserID":161632,"insertUser":{"userID":161632,"name":"SteyJ","title":"Jacob Stey","url":"https:\/\/community.smartsheet.com\/profile\/SteyJ","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5Zjq-V_rc4o!zmkrczO71bo!6kwXWfi9FUH","dateLastActive":"2023-08-13T22:08:19+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/I85D28MOQEGC\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-13T15:20:11+00:00","dateAnswered":"2023-08-12T20:33:33+00:00","acceptedAnswers":[{"commentID":390375,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/108886\/why-does-a-user-show-an-email-address\n <\/a>\n<\/div>\n

You can have them update their contact card by going to Settings -> Personal Settings<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":108879,"type":"question","name":"Not all recipients are receiving automated emails","excerpt":"Hello. I have many sheets with automation and i have one that is giving me an issue. I am getting the above email notice from smartsheets for this one automation on one sheet. I have double checked that the restrictions are set to \"Unrestricted\". See attachments. Thank you! Any ideas what is happening or how to fix it?","snippet":"Hello. I have many sheets with automation and i have one that is giving me an issue. I am getting the above email notice from smartsheets for this one automation on one sheet. I…","categoryID":321,"dateInserted":"2023-08-11T22:17:59+00:00","dateUpdated":null,"dateLastComment":"2023-08-12T15:59:01+00:00","insertUserID":165014,"insertUser":{"userID":165014,"name":"David Sprague","title":"Fire Chief","url":"https:\/\/community.smartsheet.com\/profile\/David%20Sprague","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-12T22:17:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-13T23:49:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":37,"score":null,"hot":3383650020,"url":"https:\/\/community.smartsheet.com\/discussion\/108879\/not-all-recipients-are-receiving-automated-emails","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108879\/not-all-recipients-are-receiving-automated-emails","format":"Rich","lastPost":{"discussionID":108879,"commentID":390368,"name":"Re: Not all recipients are receiving automated emails","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390368#Comment_390368","dateInserted":"2023-08-12T15:59:01+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-13T23:49:27+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-12T22:18:07+00:00","dateAnswered":"2023-08-12T15:04:58+00:00","acceptedAnswers":[{"commentID":390360,"body":"

Hi @David Sprague<\/a>, <\/p>

I hope you're well and safe!<\/p>

There's a known issue at the moment.<\/p>

I'll get back to the post if I can find the thread.<\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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=321&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":4943,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics

  • Is it possible to URL-Query an embedded form?

    I am wondering if it is possible to prefill an embedded form on a dashboard by referencing the form in the dashboard URL?

  • Why does a user show an email address?

    When I'm adding users to Assigned, it usually shows the First Name Last Name, but sometimes it just shows the email address. How do I fix this? See screenshot example.

  • Not all recipients are receiving automated emails

    Hello. I have many sheets with automation and i have one that is giving me an issue. I am getting the above email notice from smartsheets for this one automation on one sheet. I have double checked that the restrictions are set to "Unrestricted". See attachments. Thank you! Any ideas what is happening or how to fix it?

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

 Watch video