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.


Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    =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)

Answers

  • @Kelly Moore : Could you please look into this

  • Swetha
    Swetha
    edited 03/24/23

    @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.

    =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.


  • Kelly Moore
    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

  • @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.


  • @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
    Kelly Moore ✭✭✭✭✭✭
    edited 03/28/23

    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
    Swetha
    edited 03/29/23

    @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.


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

  • @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
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    =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)

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!