Formula to return text rather than a number

Hi there,

I have the following columns in use:

Plan Expiry date (Date column)

Date of Event (Date Column)

Status of Plan (Text Column with Formula)

I have used the following formula in the 'Status of Plan' column to let me know if a plan had expired when an event occurred.

=IF(ISDATE([Plan Expiry Date]@row), [Date of Event]@row - [Plan Expiry Date]@row)

The formula is working fine and gives me a positive or negative number telling me how many days until a plan expires or how many days since it has expired.

What I want to do is return a word instead of the positive or negative numbers: eg If the number less than 0, I want it to return “Current" and If the number is greater than 0, I want it to return “Expired”.

I tried nesting an If(and but was not successful.

Thanks in advance for your help.

Find more posts tagged with

Accepted answers

Mike TV

@Nat O

Try this then:

=IF(ISDATE([Plan Expiry Date]@row), IF(([Date of Event]@row - [Plan Expiry Date]@row) < 0, "Current", "Expired"), "")

All comments

Mike TV

@Nat O

Does this work for you? if it's 0 then it'll mark it as Expired as well. You can put the formula in your Status of Plan column. I just put it in a different one to show you what value your existing formula was returning.

Example sheet:

Column5 formula:

=IFERROR(IF(([Date of Event]@row - [Plan Expiry Date]@row) < 0, "Current", "Expired"), "")

If you want a zero value to be Current instead of Expired, just change the < 0 to <=0.

Nat O

Thanks for the reply, @Mike TV . I think we are on to something, but it doesn't quite work for me, just because not every row has a plan expiry date - sometimes it is blank - I just want to ignore the blank entries and the formula you suggested returns an 'expired' value for the blank ones. That was why I using IF(ISDATE in my formula, so it would only affect the rows with a plan expiry date.

Any further suggestions?

thanks

Mike TV

@Nat O

Try this then:

=IF(ISDATE([Plan Expiry Date]@row), IF(([Date of Event]@row - [Plan Expiry Date]@row) < 0, "Current", "Expired"), "")

Nat O

Thanks @Mike TV - that works a treat!

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.