Need formula to return a value for a year less than current year and less than current month

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.

Find more posts tagged with

Accepted answers

Kelly Moore

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

All comments

Swetha

@Kelly Moore : Could you please look into this

Swetha

@Paul Newcome : 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.

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.

Kelly Moore

Hey @Swetha

Try this

=IFERROR(IF(OR(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) < MONTH(TODAY()), 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)

Kelly

Swetha

@Kelly Moore : Thanks for the reply. I used the formula. If the year is less than current year and month is lesser than current month, then "NA" value is returned for months with 01 & 02. However, if If the year is less than current year and month is lesser than current month with values 03 to 12 then value is returned. I need "NA" to be returned for this scenario too. Remaining formula works fine

Can you please take a look? The row highlighted in blue needs "NA" to be returned.

Swetha

@Kelly Moore : I'm not sure, whether i have asked the formula request correctly. I'm looking for a formula, if If the year is less than current year and month is lesser than current month, value return should be "N/A" a along with other criteria's.

will try to explain with example. If year is less than current year i.e if it falls with in last one year with exact current month then value should be returned. i.e. CL2203--> CL2203. CL2206--->CL2206. However, if it is before one year then "NA" should be returned. i.e. OL2202---> NA, CL2105--->NA, DL2102--> DL2102, OL1906--> NA.

With this formula, OL1906 returns OL1906, CL2105 returns CL2105. i need NA for this condition.

Remaining part of formula works fine.

If i need to simplify, if year is with in last 365 days till date(current month) then value needs to be returned otherwise it should be NA.

Could you please take a look and help me on this.

Kelly Moore

Hey @Swetha

Try this

=IFERROR(IF(OR(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), AND(VALUE(MID(SUBSTITUTE(RI@row, " ", ""), 5, 2)) <= MONTH(TODAY()), 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)

What value do you get now?

Kelly

Swetha

@Kelly Moore : If it less than or equal to last year and month is 01,02& 03 NA is displayed. However, If it is last year i.e. 22 & Current month i.e. 03 "NA" should not be displayed, need to return value.

If it is less than last year (i.e.22) and month is 04,05,06,07,08,09,10,11&12 same value is returned. However, NA needs to be displayed.

Swetha

@Kelly Moore : Is it possible to look at this formula?

Swetha

@Kelly Moore : Is there any way you can look at this, i really need this to be fixed. Could you please let me know

Kelly Moore

Swetha

@Kelly Moore : Thank you Very much!! It worked!!!

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.