Setting status based on Date Column

Options
Todd Smelser
Todd Smelser ✭✭✭
edited 07/18/22 in Formulas and Functions

HI,

I am trying to set a status based on the variable that the Date cell@Row is NOT blank. When I use this formula, I get "Invalid Column Value"

=IFERROR(IF([Subscription End Date]@row > TODAY(), "Active", [Subscription Cancellation Date]@row <>, "Cancelled"), "")

Anyone know what I am doing wrong?

Thank you!

Best Answer

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Todd Smelser

    Looks like you missed out the "" in the formula after IF([Subscription Cancellation Date]@row <>

    Does this work for you?

    =IF([Subscription end date]@row > TODAY(), "Active", IF([Subscription Cancellation Date]@row <> "", "Cancelled", ""))

    Hope it helps

    Thanks

Answers

  • Todd Smelser
    Options

    Well, I am still having trouble with the NOT blank part but I was able to get the desired value using this formula :

    =IFERROR(IF([Subscription End Date]@row > TODAY(), "Active", IF([Subscription Cancellation Date]@row < TODAY(), "Cancelled")), "")

    Would still like to use "Not Blank" here, if it is possible

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Todd Smelser

    Looks like you missed out the "" in the formula after IF([Subscription Cancellation Date]@row <>

    Does this work for you?

    =IF([Subscription end date]@row > TODAY(), "Active", IF([Subscription Cancellation Date]@row <> "", "Cancelled", ""))

    Hope it helps

    Thanks

  • Todd Smelser
    Options

    That does work. Thank you!

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Options

    No worries easily done, glad it worked.

  • Todd Smelser
    Options

    Paul, doesn't <> "" mean "if the field is blank" though?

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

    Hi @Todd Smelser

    I hope you're well and safe!

    • <>"" means not equal to blank
    • ="" means equals blank

    Make sense?

    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.

  • Todd Smelser
    Options

    It does. However, The formula is not changing the status when the date field is not blank so I think the first argument may be overridden the second.

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Options

    Thanks @Andrée Starå

    Hi @Todd Smelser The first part of the formula only checks if the subscription end date is after today, if it is a active subscription (date in the future) the formula stops and wont check the second part? If you want the Cancelled piece to be the dominant you can flip the parts of the formula from this:

    =IF([Subscription end date]@row > TODAY(), "Active", IF([Subscription Cancellation Date]@row <> "", "Cancelled", ""))

    To this making the cancellation the dominant piece.

    = IF([Subscription Cancellation Date]@row <> "", "Cancelled", IF([Subscription end date]@row > TODAY(), "Active", ""))

    Or are you trying to show a subscription as active until its expiry date has passed and if there is anything in the cancellation date it then shows as cancelled? if so that would be this one (also added a = to include todays date):

    Formula 1 - original formula

    =IF([Subscription end date]@row >= TODAY(), "Active", IF([Subscription Cancellation Date]@row <> "", "Cancelled", ""))

    Formula 2 - flipped formula (cancellation is dominant)

    = IF([Subscription Cancellation Date]@row <> "", "Cancelled", IF([Subscription end date]@row > TODAY(), "Active", ""))

    The formulas above would give the following results based on these sample dates around todays date

    Does that help at all? if you need the formula to do anything different please explain further.

    Thanks

  • Todd Smelser
    Options

    Thanks Paul. The Flipped formula is what worked for me.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!