Calculating days until expiration when the column has text along with dates

I'm new to Smartsheet and am stumped by formulas no matter how many YouTube videos I watch or articles I read - help! I'm trying to create a column that contains the number of days until contract expiration, but my contract expiration column also has text (5 different types - NA, Undefined, Unknown, NA, Until Terminated). My formula is working for the dates but I'm getting #InvalidOperation for the rows where one of the 5 text options is listed. Here's my formula:

=[Termination Date]@row - TODAY()

I'm sure it's an easy fix for those of you adept at formulas; I'm just not one of those people. Appreciate any assistance you can provide.

Thanks,

Connie

Find more posts tagged with

Accepted answers

Andrée Starå

@ckirby

Try something like this.

=IFERROR([Termination Date]@row - TODAY(), "")

Did that work?

✅Remember! 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!

Andrée Starå

@ckirby

Excellent!

Happy to help!

All comments

Paul Newcome

Are you able to provide a screenshot for reference?

ckirby

Unfortunately, no, due to confidentiality/proprietary information.

Andrée Starå

Hi @ckirby

I hope you're well and safe!

I agree with Paul. If that's not an option. Can you share a mockup?

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!

ckirby

Sorry, I'm bad at this - don't know how to do a screen shot that doesn't capture my second screen, but here you go. Hope this helps and thanks in advance.

Andrée Starå

@ckirby

Try something like this.

=IFERROR([Termination Date]@row - TODAY(), "")

Did that work?

ckirby

@Andrée Starå that worked. It now leaves the cell blank for those contracts that have no termination date. Thanks very much! Have a great day.

Connie

Andrée Starå

@ckirby

Excellent!

Happy to help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.