Setting status based on Date Column

Todd Smelser

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!

Paul McGuinness

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

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

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

That does work. Thank you!

Paul McGuinness

No worries easily done, glad it worked.

Todd Smelser

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

Andrée Starå

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!

Todd Smelser

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

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

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