#INVALID COLUMN VALUE
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?
Best Answer
-
Paul Newcome ✭✭✭✭✭✭当使用一个日期值公式,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")
thinkspi.com
Answers
-
Andrée Starå ✭✭✭✭✭✭Hi@LGraf
我希望你很好,安全!
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
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.
-
LGraf ✭
See screenshot below.
-
Andrée Starå ✭✭✭✭✭✭
Can you also paste the formula you're testing?
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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
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?
thinkspi.com
-
Andrée Starå ✭✭✭✭✭✭
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
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.
-
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!
-
Paul Newcome ✭✭✭✭✭✭
当使用一个日期值公式,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")
thinkspi.com
Categories
=JOIN(COLLECT({EORMF - Control Range 1}, {EORMF - Control Range 1}, @cell = \"Q1\", <\/strong>{Client Name}, [Client Name]@row), \" \")<\/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":107174,"type":"question","name":"Calendar view in Dashboard","excerpt":"We have a sheet that acts as our project calendar and use start\/end date columns to show them in calendar view across multiple days (to show duration). How can I get this same view of this sheet (or the report from sheet) in a dashboard. The report widget only appears to generate a gant view.","snippet":"We have a sheet that acts as our project calendar and use start\/end date columns to show them in calendar view across multiple days (to show duration). How can I get this same…","categoryID":321,"dateInserted":"2023-07-03T14:06:41+00:00","dateUpdated":null,"dateLastComment":"2023-07-03T17:04:50+00:00","insertUserID":163057,"insertUser":{"userID":163057,"name":"alisoncholak","url":"https:\/\/community.smartsheet.com\/profile\/alisoncholak","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bCP1Gy0kSHM!Dz-yg3sWw_0!KaCecJdROAp","dateLastActive":"2023-07-03T19:36:07+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-03T20:15:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":22,"score":null,"hot":3376797691,"url":"https:\/\/community.smartsheet.com\/discussion\/107174\/calendar-view-in-dashboard","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107174\/calendar-view-in-dashboard","format":"Rich","tagIDs":[241,292],"lastPost":{"discussionID":107174,"commentID":383549,"name":"Re: Calendar view in Dashboard","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383549#Comment_383549","dateInserted":"2023-07-03T17:04: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-03T20:15:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-03T19:36:05+00:00","dateAnswered":"2023-07-03T17:04:50+00:00","acceptedAnswers":[{"commentID":383549,"body":" You could publish the sheet as either Read Only - Full or Edit By Anyone and set the default view to calendar view. Then you would use the publish URL in a Web Content Widget.<\/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":241,"urlcode":"Calendar","name":"Calendar View"},{"tagID":292,"urlcode":"Dashboards","name":"Dashboards"}]},{"discussionID":107147,"type":"question","name":"Using IFERROR Formula - Still getting UNPARSEABLE error","excerpt":"I'm calculating a Burn Rate Percentage so it's using a basic calculation FORMULA =[Column4]@row \/ [Column2]@row --> This works fine However when one of the Column is a ZERO, I get a #DIVDE BY ZERRO error displayed. To address this error, I used an IFERROR but it's giving me an \"UNPARSEABLE\" error. I want the cell to…","snippet":"I'm calculating a Burn Rate Percentage so it's using a basic calculation FORMULA =[Column4]@row \/ [Column2]@row --> This works fine However when one of the Column is a ZERO, I get…","categoryID":343,"dateInserted":"2023-06-30T16:32:54+00:00","dateUpdated":"2023-06-30T16:35:50+00:00","dateLastComment":"2023-06-30T18:58:03+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-01T00:40:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-03T20:15:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3376296057,"url":"https:\/\/community.smartsheet.com\/discussion\/107147\/using-iferror-formula-still-getting-unparseable-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107147\/using-iferror-formula-still-getting-unparseable-error","format":"Rich","lastPost":{"discussionID":107147,"commentID":383396,"name":"Re: Using IFERROR Formula - Still getting UNPARSEABLE error","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383396#Comment_383396","dateInserted":"2023-06-30T18:58:03+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-03T20:15:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T18:53:59+00:00","dateAnswered":"2023-06-30T18:25:40+00:00","acceptedAnswers":[{"commentID":383387,"body":" You need to remove the closing parenthesis from before the comma.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","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=341&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":5437,"limit":3},"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
