Need a formula to verify with system date and return a value

I have column "Source" with text values(some text with numbers too) and date values.. I need text value to be return as it is in "Target" column. However, for the date values, it need to compare with System date.

If year is greater than the current year and equal to current year but month is equal to or greater then current month value return should be "N/A".

If the year is less than current year and equal to current year but less than current month, exact value from source column should be returned.

Also, the values which does not have date format, need to return the exact source column value.

I tried with below formula but not working:

=IF(VALUE(MID(Source1, 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), "N/A", IF(VALUE(MID(Source1 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), IF(VALUE(MID(Source1, 5, 2)) > VALUE(MONTH(TODAY())), "N/A", IF(VALUE(MID(Source1 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)),IF(VALUE(MID(Source1, 5, 2)) = VALUE(MONTH(TODAY())), "N/A"), Source1)))).

I need the values to return as mentioned below:

Find more posts tagged with

Accepted answers

Katie G

Hi @Swetha -- I added in a SUBSTITUTE formula to deal with the fact that there is a row with an extra space between 23 and 06. I also used an AND formula to check both year and date at the same time as the second IF statement.

=IFERROR(IF(VALUE(MID(SUBSTITUTE(Source@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), "N/A", IF(AND(VALUE(MID(SUBSTITUTE(Source@row, " ", ""), 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), VALUE(MID(SUBSTITUTE(Source@row, " ", ""), 5, 2)) > VALUE(MONTH(TODAY()))), "N/A", Source@row)), Source@row)

Just know that if you have other variations in the source column beyond the examples you showed, the formula may not hold up across all of them.

All comments

Swetha

@Andrée Starå /@Paul Newcome /@Nick Korna : Any one can help me with the formula please.

Swetha

@Bassam Khalil : Can you help me with formula for above please

Katie G

Just know that if you have other variations in the source column beyond the examples you showed, the formula may not hold up across all of them.

Swetha

@Katie G : Thanks for the formula!!! But i do have other variation along with AL and BL values such as LANE2204 APR, LANE2308 AUG,LANE2412 DEC etc....

Since AL & BL is 2 characters formula works. Could you please help me on how to incorporate 4 character variations for this formula.

Can you please help me with this.

Swetha

@Katie G : Is it possible for you take a look at the formula which you provided earlier

Can you please look into this formula too. i need to add one more scenario for existing formula.

If the year is less than current year and month is lesser than current month, value return should be "N/A" along with the below mentioned combinations.

However, i have formula for below mentioned combination; i just need to add the above scenario for the formula.

=IFERROR(IF(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), "NA", IF(AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) = VALUE(RIGHT(YEAR(TODAY()), 2)), VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) > VALUE(MONTH(TODAY()))), "NA", RI@row)), RI@row)

If year is greater than the current year and equal to current year but month is greater then current month value return is "N/A".

If the year is equal to current year but less than or equal to current month, exact value from RI column should be returned.

If the year is less than current year and month is equal to or greater than current month, exact value from RI column should be returned.

In the below example for the row highlighted in yellow; NA needs to be displayed.

Genevieve P.

Hey @Swetha

It looks like Kelly responded to you on this other post:

https://community.smartsheet.com/discussion/comment/368951#Comment_368951

Cheers,

Genevieve

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.