#INVALID COLUMN VALUE

Options
LGraf
LGraf ✭✭✭✭
edited 05/18/23 in Smartsheet 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:

Best Answer

Answers

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

    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 by marking 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • LGraf
    LGraf ✭✭✭✭
    Options

    See screenshot below.


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

    @LGraf

    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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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?

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

    @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

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • LGraf
    LGraf ✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    When using a date value in a formula, 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")